﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.DomainObject.Administration.UserManagement;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;

namespace SIPO.Library.DataAccess.Transaction
{
    public partial class recRegistrationRepo : ISearchable<recRegistration>
    {
        public object SelectScalar(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM transactions.recRegistration trr ", column);
                            sbQuery.AppendFormat("      inner join transactions.recSectorRegistration trsr on trr.RegistrationID=trsr.RegistrationID ");
                            sbQuery.AppendFormat("      inner join transactions.recInvoice tri on tri.InvoiceNo=trsr.InvoiceNo ");
                            sbQuery.AppendFormat("      inner join transactions.recInvoiceDetail trid on trid.InvoiceNo=trsr.InvoiceNo ");
                            sbQuery.AppendFormat("      inner join master.recTariff mrt on mrt.TariffID=trid.TariffID ");
                            sbQuery.AppendFormat("      left join master.recImpositionBase mri on mri.ImpositionBaseID=mrt.ImpositionBaseID ");
                            sbQuery.AppendFormat("      inner join master.recSubSubSector mrss on mrss.SubSubSectorID=trsr.SubSubSectorID ");
                            sbQuery.AppendFormat("      inner join master.recRegistrationStatus mrr on mrr.RegistrationStatusID=trsr.RegistrationStatusID {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FROM transactions.recRegistration trr ", column);
                            sbQuery.AppendFormat("      inner join transactions.recSectorRegistration trsr on trr.RegistrationID=trsr.RegistrationID ");
                            sbQuery.AppendFormat("      inner join transactions.recInvoice tri on tri.InvoiceNo=trsr.InvoiceNo ");
                            sbQuery.AppendFormat("      inner join transactions.recInvoiceDetail trid on trid.InvoiceNo=trsr.InvoiceNo ");
                            sbQuery.AppendFormat("      inner join master.recTariff mrt on mrt.TariffID=trid.TariffID ");
                            sbQuery.AppendFormat("      left join master.recImpositionBase mri on mri.ImpositionBaseID=mrt.ImpositionBaseID ");
                            sbQuery.AppendFormat("      inner join master.recSubSubSector mrss on mrss.SubSubSectorID=trsr.SubSubSectorID ");
                            sbQuery.AppendFormat("      inner join master.recRegistrationStatus mrr on mrr.RegistrationStatusID=trsr.RegistrationStatusID {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM transactions.recRegistration trr ", column);
                            sbQuery.AppendFormat("      inner join transactions.recSectorRegistration trsr on trr.RegistrationID=trsr.RegistrationID ");
                            sbQuery.AppendFormat("      inner join transactions.recInvoice tri on tri.InvoiceNo=trsr.InvoiceNo ");
                            sbQuery.AppendFormat("      inner join transactions.recInvoiceDetail trid on trid.InvoiceNo=trsr.InvoiceNo ");
                            sbQuery.AppendFormat("      inner join master.recTariff mrt on mrt.TariffID=trid.TariffID ");
                            sbQuery.AppendFormat("      left join master.recImpositionBase mri on mri.ImpositionBaseID=mrt.ImpositionBaseID ");
                            sbQuery.AppendFormat("      inner join master.recSubSubSector mrss on mrss.SubSubSectorID=trsr.SubSubSectorID ");
                            sbQuery.AppendFormat("      inner join master.recRegistrationStatus mrr on mrr.RegistrationStatusID=trsr.RegistrationStatusID {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM (select trsr.SubSubSectorID,trsr.SectorRegistrationID, trr.RegistrationID, mrss.SubSubSectorName, recsubsec.SubSectorName,mrs.SectorName,trr.PreDebtorName,trsr.EmisionValue,trid.Value,tri.InvoiceNo,tri.PaymentNo,mrr.RegistrationStatusName,trr.IsActive,RegistrationEmisionTypeName +' '+ EmisionTypeName  RegistrationEmisionTypeName from transactions.recRegistration trr ", column);
                            sbQuery.AppendFormat("      inner join transactions.recSectorRegistration trsr on trr.RegistrationID=trsr.RegistrationID ");
                            sbQuery.AppendFormat("      inner join transactions.recInvoice tri on tri.InvoiceNo=trsr.InvoiceNo ");
                            sbQuery.AppendFormat("      inner join transactions.recInvoiceDetail trid on trid.InvoiceNo=trsr.InvoiceNo ");
                            sbQuery.AppendFormat("      inner join master.recTariff mrt on mrt.TariffID=trid.TariffID ");
                            sbQuery.AppendFormat("      left join master.recImpositionBase mri on mri.ImpositionBaseID=mrt.ImpositionBaseID ");
                            sbQuery.AppendFormat("      inner join master.recSubSubSector mrss on mrss.SubSubSectorID=trsr.SubSubSectorID ");
                            sbQuery.AppendFormat("      inner join master.recSector mrs on mrs.SectorID = trsr.SectorID ");
                            sbQuery.AppendFormat("      inner join master.recSubSector recsubsec on  recsubsec.SubSectorID = trsr.SubSectorID  ");
                            sbQuery.AppendFormat("      inner join master.recRegistrationStatus mrr on mrr.RegistrationStatusID=trsr.RegistrationStatusID ");
                            sbQuery.AppendFormat("      left join master.recRegistrationEmisionType mrret on mrret.RegistrationEmisionTypeID = trsr.RegistrationEmisionTypeID ");
                            sbQuery.AppendFormat("      left join master.recEmisionType mret on mret.EmisionTypeID=trsr.EmisionTypeID ) a  {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM transactions.recRegistration trr ", column);
                            sbQuery.AppendFormat("      inner join transactions.recSectorRegistration trsr on trr.RegistrationID=trsr.RegistrationID ");
                            sbQuery.AppendFormat("      inner join transactions.recInvoice tri on tri.InvoiceNo=trsr.InvoiceNo ");
                            sbQuery.AppendFormat("      inner join transactions.recInvoiceDetail trid on trid.InvoiceNo=trsr.InvoiceNo ");
                            sbQuery.AppendFormat("      inner join master.recTariff mrt on mrt.TariffID=trid.TariffID ");
                            sbQuery.AppendFormat("      left join master.recImpositionBase mri on mri.ImpositionBaseID=mrt.ImpositionBaseID ");
                            sbQuery.AppendFormat("      inner join master.recSubSubSector mrss on mrss.SubSubSectorID=trsr.SubSubSectorID ");
                            sbQuery.AppendFormat("      inner join master.recRegistrationStatus mrr on mrr.RegistrationStatusID=trsr.RegistrationStatusID {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM transactions.recRegistration trr ", column);
                            sbQuery.AppendFormat("      inner join transactions.recSectorRegistration trsr on trr.RegistrationID=trsr.RegistrationID ");
                            sbQuery.AppendFormat("      inner join transactions.recInvoice tri on tri.InvoiceNo=trsr.InvoiceNo ");
                            sbQuery.AppendFormat("      inner join transactions.recInvoiceDetail trid on trid.InvoiceNo=trsr.InvoiceNo ");
                            sbQuery.AppendFormat("      inner join master.recTariff mrt on mrt.TariffID=trid.TariffID ");
                            sbQuery.AppendFormat("      left join master.recImpositionBase mri on mri.ImpositionBaseID=mrt.ImpositionBaseID ");
                            sbQuery.AppendFormat("      inner join master.recSubSubSector mrss on mrss.SubSubSectorID=trsr.SubSubSectorID ");
                            sbQuery.AppendFormat("      inner join master.recRegistrationStatus mrr on mrr.RegistrationStatusID=trsr.RegistrationStatusID {0}", parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public List<recRegistration> SetListReport(int pageSize, int currentPage, string parameters)
        {
            List<recRegistration> tt = new List<recRegistration>();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat(" SELECT distinc");

                    sbQuery.AppendFormat(" recRegristation.RegristationID, recRegistration.PreDebtorName, recRegistration.NPWP, recSector.SectorName, recSubSector.SubSectorName, recSubSubSector.SubSubSectorName, recRegistration.Address1, recRegistration.Address2, recRegistration.Address3, recRegion.RegionName, recCity.CityName, recRegistration.ZipCode, recRegistration.Phone, recRegistration.Website");
                    sbQuery.AppendFormat(" FROM ");
                    sbQuery.AppendFormat(" transactions.recRegistration ");
                    sbQuery.AppendFormat(" inner join master.recRegion on transactions.recRegistration.RegionID = master.recRegion.RegionID ");
                    sbQuery.AppendFormat(" inner join master.recCity on transactions.recRegistration.CityID = master.recCity.RegionID ");
                    sbQuery.AppendFormat(" inner join transactions.recSectorRegistration on transactions.recRegistration.RegistrationID = transactions.recSectorRegistration.RegistrationID ");
                    sbQuery.AppendFormat(" inner join master.recSector on transactions.recSectorRegistration.SectorID = master.recSector.SectorID ");
                    sbQuery.AppendFormat(" inner join master.recSubSector on transactions.recSectorRegistration.SubSectorID = master.recSubSector.SubSectorID ");
                    sbQuery.AppendFormat(" inner join master.recSubSubSector on transactions.recSectorRegistration.SubSubSectorID = master.recSubSubSector.SubSubSectorID ");
                    sbQuery.AppendFormat(" {0} ", parameters);
                    sbQuery.AppendFormat(" ) ");

                    sbQuery.AppendFormat(" SELECT * from result_set ");

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recRegistration t = new recRegistration(true);
                                t.RegistrationID = dr[recRegistration.Fields.RegistrationID] == DBNull.Value ? 0 : int.Parse(dr[recRegistration.Fields.RegistrationID].ToString());
                                t.PreDebtorName = dr[recRegistration.Fields.PreDebtorName] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.PreDebtorName].ToString();
                                t.NPWP = dr[recRegistration.Fields.NPWP] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.NPWP].ToString();
                                t.recSector.SectorName = dr[recSector.Fields.SectorName] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorName].ToString();
                                t.recSubSector.SubSectorName = dr[recSubSector.Fields.SubSectorName] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorName].ToString();
                                t.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();
                                t.Address1 = dr[recRegistration.Fields.Address1] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Address1].ToString();
                                t.Address2 = dr[recRegistration.Fields.Address2] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Address2].ToString();
                                t.Address3 = dr[recRegistration.Fields.Address3] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Address3].ToString();
                                t.recRegion.RegionName = dr[recRegion.Fields.RegionName] == DBNull.Value ? string.Empty : dr[recRegion.Fields.RegionName].ToString();
                                t.recCity.CityName = dr[recCity.Fields.CityName] == DBNull.Value ? string.Empty : dr[recCity.Fields.CityName].ToString();
                                t.ZipCode = dr[recRegistration.Fields.ZipCode] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.ZipCode].ToString();
                                t.Phone = dr[recRegistration.Fields.Phone] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Phone].ToString();
                                t.Website = dr[recRegistration.Fields.Website] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Website].ToString();

                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }


        public List<recRegistration> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recRegistration> tt = new List<recRegistration>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], * from (select ", sortName, sortOrder);
                    sbQuery.AppendFormat("      trsr.SubSubSectorID,trsr.SectorRegistrationID, trr.RegistrationID, mrss.SubSubSectorName,recsubsec.SubSectorName,mrs.SectorName,trr.PreDebtorName,trsr.EmisionValue,trid.Value,tri.InvoiceNo,tri.PaymentNo,mrr.RegistrationStatusName,trr.IsActive,RegistrationEmisionTypeName +' '+ EmisionTypeName  RegistrationEmisionTypeName ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      transactions.recRegistration trr ");
                    sbQuery.AppendFormat("      inner join transactions.recSectorRegistration trsr on trr.RegistrationID=trsr.RegistrationID ");
                    sbQuery.AppendFormat("      inner join transactions.recInvoice tri on tri.InvoiceNo=trsr.InvoiceNo ");
                    sbQuery.AppendFormat("      inner join transactions.recInvoiceDetail trid on trid.InvoiceNo=trsr.InvoiceNo ");
                    sbQuery.AppendFormat("      inner join master.recTariff mrt on mrt.TariffID=trid.TariffID ");
                    sbQuery.AppendFormat("      inner join master.recSubSubSector mrss on mrss.SubSubSectorID=trsr.SubSubSectorID ");
                    sbQuery.AppendFormat("      inner join master.recSector mrs on mrs.SectorID = trsr.SectorID ");
                    sbQuery.AppendFormat("      inner join master.recSubSector recsubsec on  recsubsec.SubSectorID = trsr.SubSectorID  ");
                    sbQuery.AppendFormat("      inner join master.recRegistrationStatus mrr on mrr.RegistrationStatusID=trsr.RegistrationStatusID ");
                    sbQuery.AppendFormat("      left join master.recRegistrationEmisionType mrret on mrret.RegistrationEmisionTypeID = trsr.RegistrationEmisionTypeID ");
                    sbQuery.AppendFormat("      left join master.recEmisionType mret on mret.EmisionTypeID=trsr.EmisionTypeID ) a ");
                    sbQuery.AppendFormat(" {0} ", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recRegistration t = new recRegistration(true);
                                t.RegistrationID = dr[recRegistration.Fields.RegistrationID] == DBNull.Value ? 0 : int.Parse(dr[recRegistration.Fields.RegistrationID].ToString());
                                t.PreDebtorName = dr[recRegistration.Fields.PreDebtorName] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.PreDebtorName].ToString();
                                t.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();
                                t.recSubSector.SubSectorName = dr[recSubSector.Fields.SubSectorName] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorName].ToString();
                                t.recSector.SectorName = dr[recSector.Fields.SectorName] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorName].ToString();
                                t.recInvoiceDetail.Value = dr[recInvoiceDetail.Fields.Value] == DBNull.Value ? 0 : Convert.ToDecimal(dr[recInvoiceDetail.Fields.Value].ToString());
                                t.recInvoice.InvoiceNo = dr[recInvoice.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.InvoiceNo].ToString();
                                t.recInvoice.PaymentNo = dr[recInvoice.Fields.PaymentNo] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.PaymentNo].ToString();
                                t.IsActive = dr[recRegistration.Fields.IsActive] == DBNull.Value ? (bool?)null : bool.Parse(dr[recRegistration.Fields.IsActive].ToString());
                                t.recRegistrationStatus.RegistrationStatusName = dr[recRegistrationStatus.Fields.RegistrationStatusName] == DBNull.Value ? string.Empty : dr[recRegistrationStatus.Fields.RegistrationStatusName].ToString();
                                t.recSectorRegistration = new recSectorRegistration(true);
                                t.recSectorRegistration.EmisionValue = dr[recSectorRegistration.Fields.EmisionValue] == DBNull.Value ? 0 : Convert.ToDecimal(dr[recSectorRegistration.Fields.EmisionValue].ToString());
                                t.recSectorRegistration.SectorRegistrationID = dr[recSectorRegistration.Fields.SectorRegistrationID] == DBNull.Value ? 0 : int.Parse(dr[recSectorRegistration.Fields.SectorRegistrationID].ToString());
                                t.recSectorRegistration.recRegistrationEmisionType.RegistrationEmisionTypeName = dr["RegistrationEmisionTypeName"] == DBNull.Value ? string.Empty : dr["RegistrationEmisionTypeName"].ToString();
                                //t.recImpositionBase.ImpositonBaseName = dr[recImpositionBase.Fields.ImpositonBaseName] == DBNull.Value ? string.Empty : dr[recImpositionBase.Fields.ImpositonBaseName].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
        public bool Save(recRegistration domain, recSectorRegistration details, recUser user, recUserSector userSector, recInvoice invoice, recInvoiceDetail invoiceDetail, recBilling billing, recUserRole userRole)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strCommandMaster = "INSERT INTO transactions.recRegistration(PreDebtorName, NPWP, Address1, Address2, Address3, RegionID, CityID, ZipCode, Email, Phone, Fax, Website, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate, IsActive, IsIndividual) VALUES(@PreDebtorName, @NPWP, @Address1, @Address2, @Address3, @RegionID, @CityID, @ZipCode, @Email, @Phone, @Fax, @Website, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate, @IsActive, @IsIndividual)";
                    SqlCommand cmd = new SqlCommand(strCommandMaster, conn);
                    cmd.Parameters.Add("@PreDebtorName", SqlDbType.VarChar).Value = domain.PreDebtorName;
                    cmd.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = domain.NPWP;
                    cmd.Parameters.Add("@Address1", SqlDbType.VarChar).Value = (object)domain.Address1 ?? DBNull.Value;
                    cmd.Parameters.Add("@Address2", SqlDbType.VarChar).Value = (object)domain.Address2 ?? DBNull.Value;
                    cmd.Parameters.Add("@Address3", SqlDbType.VarChar).Value = (object)domain.Address3 ?? DBNull.Value;
                    cmd.Parameters.Add("@RegionID", SqlDbType.Int).Value = (object)domain.RegionID ?? DBNull.Value;
                    cmd.Parameters.Add("@CityID", SqlDbType.Int).Value = (object)domain.CityID ?? DBNull.Value;
                    cmd.Parameters.Add("@ZipCode", SqlDbType.VarChar).Value = (object)domain.ZipCode ?? DBNull.Value;
                    cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)domain.Email ?? DBNull.Value;
                    cmd.Parameters.Add("@Phone", SqlDbType.VarChar).Value = (object)domain.Phone ?? DBNull.Value;
                    cmd.Parameters.Add("@Fax", SqlDbType.VarChar).Value = (object)domain.Fax ?? DBNull.Value;
                    cmd.Parameters.Add("@Website", SqlDbType.VarChar).Value = (object)domain.Website ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domain.CreatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)domain.CreatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)domain.IsActive ?? DBNull.Value;
                    cmd.Parameters.Add("@IsIndividual", SqlDbType.Bit).Value = (object)domain.IsIndividual ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // Save Master
                    cmd.Dispose();

                    string strCommandGetId = " SELECT MAX(RegistrationId) RegistrationId FROM transactions.recRegistration WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetId = new SqlCommand(strCommandGetId, conn);
                    cmdGetId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;
                    cmdGetId.Transaction = txn;
                    var obj = cmdGetId.ExecuteScalar(); // Get Registration Id 
                    cmdGetId.Dispose();

                    decimal RegistrationId = obj == DBNull.Value ? 0m : Convert.ToDecimal(obj.ToString()); // Get Master Id

                    string strCommandDetail = "INSERT INTO transactions.recSectorRegistration(SectorID, SubSectorID, SubSubSectorID, IsApproved, ApprovedDate, ApprovedBy, InvoiceNo, InvoiceDate, InvoiceValue, PaymentNo, PaymentDate, PaymentValue, RegistrationStatusID, DebtorSectorID, RegistrationID, RegistrationDate, EmisionTypeID, EmisionValue, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, RegistrationEmisionTypeID) VALUES(@SectorID, @SubSectorID, @SubSubSectorID, @IsApproved, @ApprovedDate, @ApprovedBy, @InvoiceNo, @InvoiceDate, @InvoiceValue, @PaymentNo, @PaymentDate, @PaymentValue, @RegistrationStatusID, @DebtorSectorID, @RegistrationID, @RegistrationDate, @EmisionTypeID, @EmisionValue, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy, @RegistrationEmisionTypeID)";
                    SqlCommand cmdDetail = new SqlCommand(strCommandDetail, conn);
                    cmdDetail.Parameters.Add("@SectorID", SqlDbType.Int).Value = details.SectorID;
                    cmdDetail.Parameters.Add("@SubSectorID", SqlDbType.Int).Value = details.SubSectorID;
                    cmdDetail.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = details.SubSubSectorID;
                    cmdDetail.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = (object)details.IsApproved ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@ApprovedDate", SqlDbType.DateTime).Value = (object)details.ApprovedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@ApprovedBy", SqlDbType.VarChar).Value = (object)details.ApprovedBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)details.InvoiceNo ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = (object)details.InvoiceDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceValue", SqlDbType.Decimal).Value = (object)details.InvoiceValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)details.PaymentNo ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)details.PaymentDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)details.PaymentValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@RegistrationStatusID", SqlDbType.Int).Value = details.RegistrationStatusID;
                    cmdDetail.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)details.DebtorSectorID ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@RegistrationID", SqlDbType.Int).Value = Convert.ToInt32(RegistrationId);
                    cmdDetail.Parameters.Add("@RegistrationDate", SqlDbType.DateTime).Value = (object)details.RegistrationDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@EmisionTypeID", SqlDbType.SmallInt).Value = (object)details.EmisionTypeID ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@EmisionValue", SqlDbType.Money).Value = (object)details.EmisionValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)details.CreatedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)details.CreatedBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)details.UpdatedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)details.UpdatedBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@RegistrationEmisionTypeID", SqlDbType.SmallInt).Value = (object)details.RegistrationEmisionTypeID ?? DBNull.Value;

                    cmdDetail.Transaction = txn;
                    cmdDetail.ExecuteNonQuery(); // Save Detail
                    cmdDetail.Dispose();

                    string strCommandUser = "INSERT INTO master.recUser(UserCode, UserName, Email, Password, IsActive, Mobile, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate, UserTypeID) VALUES(@UserCode, @UserName, @Email, @Password, @IsActive, @Mobile, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate, @UserTypeID)";
                    SqlCommand cmdUser = new SqlCommand(strCommandUser, conn);
                    cmdUser.Parameters.Add("@UserCode", SqlDbType.VarChar).Value = user.UserCode;
                    cmdUser.Parameters.Add("@UserName", SqlDbType.VarChar).Value = (object)user.UserName ?? DBNull.Value;
                    cmdUser.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)user.Email ?? DBNull.Value;
                    cmdUser.Parameters.Add("@Password", SqlDbType.VarChar).Value = user.Password;
                    cmdUser.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)user.IsActive ?? DBNull.Value;
                    cmdUser.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = (object)user.Mobile ?? DBNull.Value;
                    cmdUser.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)user.CreatedBy ?? DBNull.Value;
                    cmdUser.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)user.CreatedDate ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)user.UpdatedBy ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)user.UpdatedDate ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)user.UserTypeID ?? DBNull.Value;
                    cmdUser.Transaction = txn;
                    cmdUser.ExecuteNonQuery();
                    cmdUser.Dispose();

                    string strCommandGetUserId = " SELECT MAX(UserID) UserID FROM master.recUser WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetUserId = new SqlCommand(strCommandGetUserId, conn);
                    cmdGetUserId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = user.CreatedBy;
                    cmdGetUserId.Transaction = txn;
                    var objUserId = cmdGetUserId.ExecuteScalar(); // Get User Id
                    cmdGetUserId.Dispose();

                    decimal UserId = objUserId == DBNull.Value ? 0m : Convert.ToDecimal(objUserId.ToString()); // Get User Id

                    string strCommandGetSectorRegistrationId = " SELECT MAX(SectorRegistrationID) SectorRegistrationID FROM transactions.recSectorRegistration WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetSectorRegistrationId = new SqlCommand(strCommandGetSectorRegistrationId, conn);
                    cmdGetSectorRegistrationId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = details.CreatedBy;
                    cmdGetSectorRegistrationId.Transaction = txn;
                    var objSectorRegistrationId = cmdGetSectorRegistrationId.ExecuteScalar(); // Get SectorRegistrationId
                    cmdGetSectorRegistrationId.Dispose();

                    decimal SectorRegistrationId = objSectorRegistrationId == DBNull.Value ? 0m : Convert.ToDecimal(objSectorRegistrationId.ToString()); // Get SectorRegistrationId

                    string strCommandUserSector = "INSERT INTO master.recUserSector(RegisterSectorID, DebtorSectorID, SubSubSectorID, UserTypeID, UserID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@RegisterSectorID, @DebtorSectorID, @SubSubSectorID, @UserTypeID, @UserID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdUserSector = new SqlCommand(strCommandUserSector, conn);
                    cmdUserSector.Parameters.Add("@RegisterSectorID", SqlDbType.Int).Value = Convert.ToInt32(SectorRegistrationId);
                    cmdUserSector.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)userSector.DebtorSectorID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = details.SubSubSectorID;
                    cmdUserSector.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)userSector.UserTypeID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UserID", SqlDbType.Int).Value = Convert.ToInt32(UserId);
                    cmdUserSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)userSector.CreatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)userSector.CreatedBy ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)userSector.UpdatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)userSector.UpdatedBy ?? DBNull.Value;
                    cmdUserSector.Transaction = txn;
                    cmdUserSector.ExecuteNonQuery();
                    cmdUserSector.Dispose();

                    string strInvoice = "INSERT INTO transactions.recInvoice(InvoiceNo, InvoiceDate, InvoiceValue, BankID, BeSpelled, PaymentNo, PaymentDate, PaymentValue, IsHapusBuku, IsHapusTagih, DebtorSectorID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, Balance) VALUES(@InvoiceNo, @InvoiceDate, @InvoiceValue, @BankID, @BeSpelled, @PaymentNo, @PaymentDate, @PaymentValue, @IsHapusBuku, @IsHapusTagih, @DebtorSectorID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy, @Balance)";
                    SqlCommand cmdInvoice = new SqlCommand(strInvoice, conn);
                    cmdInvoice.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoice.InvoiceNo;
                    //cmdInvoice.Parameters.Add("@PeriodYear", SqlDbType.Int).Value = invoice.PeriodYear;
                    //cmdInvoice.Parameters.Add("@PeriodMonth", SqlDbType.SmallInt).Value = invoice.PeriodMonth;
                    //cmdInvoice.Parameters.Add("@TransDate", SqlDbType.DateTime).Value = invoice.TransDate;
                    cmdInvoice.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = invoice.InvoiceDate;
                    cmdInvoice.Parameters.Add("@InvoiceValue", SqlDbType.Money).Value = (object)invoice.InvoiceValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@BankID", SqlDbType.SmallInt).Value = invoice.BankID;
                    cmdInvoice.Parameters.Add("@BeSpelled", SqlDbType.VarChar).Value = (object)invoice.BeSpelled ?? DBNull.Value;
                    //cmdInvoice.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)invoice.ForPayment ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)invoice.PaymentNo ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)invoice.PaymentDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)invoice.PaymentValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@IsHapusBuku", SqlDbType.Bit).Value = (object)invoice.IsHapusBuku ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@IsHapusTagih", SqlDbType.Bit).Value = (object)invoice.IsHapusTagih ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)invoice.DebtorSectorID ?? -1;
                    //cmdInvoice.Parameters.Add("@PostingDate", SqlDbType.DateTime).Value = (object)invoice.PostingDate ?? DBNull.Value;
                    //cmdInvoice.Parameters.Add("@PostingBy", SqlDbType.VarChar).Value = (object)invoice.PostingBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)invoice.CreatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)invoice.CreatedBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)invoice.UpdatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoice.UpdatedBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@Balance", SqlDbType.Money).Value = (object)invoice.Balance ?? DBNull.Value;
                    cmdInvoice.Transaction = txn;
                    cmdInvoice.ExecuteNonQuery();//save invoice
                    cmdInvoice.Dispose();

                    string strInvoiceDetail = "INSERT INTO transactions.recInvoiceDetail(InvoiceNo, Value, TransTypeID, TariffID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, ForPayment, DueDate) VALUES(@InvoiceNo, @Value, @TransTypeID, @TariffID, @CreateDate, @CreateBy, @UpdateDate, @UpdateBy, @ForPayment, @DueDate)";
                    SqlCommand cmdInvoiceDetail = new SqlCommand(strInvoiceDetail, conn);
                    //cmdInvoiceDetail.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = invoiceDetail.InvoiceDetailID;
                    cmdInvoiceDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoiceDetail.InvoiceNo;
                    cmdInvoiceDetail.Parameters.Add("@Value", SqlDbType.Money).Value = invoiceDetail.Value;
                    cmdInvoiceDetail.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = invoiceDetail.TransTypeID;
                    cmdInvoiceDetail.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)invoiceDetail.TariffID ?? DBNull.Value;
                    //cmdInvoiceDetail.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = (object)invoiceDetail.CreatedDate ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = (object)invoiceDetail.CreatedBy ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)invoiceDetail.UpdatedDate ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)invoiceDetail.UpdatedBy ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)invoiceDetail.ForPayment ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = (object)invoiceDetail.DueDate ?? DBNull.Value;
                    cmdInvoiceDetail.Transaction = txn;
                    cmdInvoiceDetail.ExecuteNonQuery();//save invoice detail
                    cmdInvoiceDetail.Dispose();

                    string strGetInvoiceDetailId = " SELECT MAX(InvoiceDetailID) InvoiceDetailID FROM transactions.recInvoiceDetail WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetInvoiceDetailId = new SqlCommand(strGetInvoiceDetailId, conn);
                    cmdGetInvoiceDetailId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = invoiceDetail.CreatedBy;
                    cmdGetInvoiceDetailId.Transaction = txn;
                    var objInvoiceDetailId = cmdGetInvoiceDetailId.ExecuteScalar(); // Get SectorRegistrationId
                    cmdGetInvoiceDetailId.Dispose();

                    decimal InvoiceDetailId = objInvoiceDetailId == DBNull.Value ? 0m : Convert.ToDecimal(objInvoiceDetailId.ToString()); // Get InvoiceDetailId

                    string strBilling = "INSERT INTO transactions.recBilling(BillingNo, DebtorSectorID, TransTypeID, TariffID, Value, InvoiceNo, InvoiceDetailID, BillingDate,CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, DueDate, ForPayment, Balance) VALUES(@BillingNo, @DebtorSectorID, @TransTypeID, @TariffID, @Value, @InvoiceNo, @InvoiceDetailID, @BillingDate, @CreateDate, @CreateBy, @UpdateDate, @UpdateBy, @DueDate, @ForPayment, @Balance)";
                    SqlCommand cmdBilling = new SqlCommand(strBilling, conn);
                    cmdBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = billing.DebtorSectorID;
                    cmdBilling.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = billing.TransTypeID;
                    cmdBilling.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)billing.TariffID ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@Value", SqlDbType.Money).Value = billing.Value;
                    cmdBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)billing.InvoiceNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = (object)InvoiceDetailId ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@BillingDate", SqlDbType.DateTime).Value = (object)billing.BillingDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = (object)billing.CreatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = (object)billing.CreatedBy ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)billing.UpdatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)billing.UpdatedBy ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = (object)billing.DueDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)billing.ForPayment ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@Balance", SqlDbType.Money).Value = (object)billing.Balance ?? DBNull.Value;
                    cmdBilling.Transaction = txn;
                    cmdBilling.ExecuteNonQuery();//save billing
                    cmdBilling.Dispose();

                    string struserrole = "INSERT INTO master.recUserRole(UserID, RoleID, CreatedDate, CreatedBy) VALUES(@UserID, @RoleID, @CreatedDate, @CreatedBy)";
                    SqlCommand cmdUserRole = new SqlCommand(struserrole, conn);
                    cmdUserRole.Parameters.Add("@UserID", SqlDbType.Int).Value = Convert.ToInt32(UserId);
                    cmdUserRole.Parameters.Add("@RoleID", SqlDbType.Int).Value = (object)userRole.RoleID ?? DBNull.Value;
                    cmdUserRole.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)userRole.CreatedDate ?? DBNull.Value;
                    cmdUserRole.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)userRole.CreatedBy ?? DBNull.Value;
                    cmdUserRole.Transaction = txn;
                    cmdUserRole.ExecuteNonQuery();//save user role
                    cmdUserRole.Dispose();

                    string strUpdateInvoiceDetail = "UPDATE transactions.recInvoiceDetail SET BillingNo = @BillingNo ,UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy where InvoiceNo = @InvoiceNo ";
                    SqlCommand cmdUpdateInvoiceDetail = new SqlCommand(strUpdateInvoiceDetail, conn);
                    cmdUpdateInvoiceDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoiceDetail.InvoiceNo;
                    cmdUpdateInvoiceDetail.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdUpdateInvoiceDetail.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = DateTime.Now;
                    cmdUpdateInvoiceDetail.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoiceDetail.CreatedBy ?? DBNull.Value;
                    cmdUpdateInvoiceDetail.Transaction = txn;
                    cmdUpdateInvoiceDetail.ExecuteNonQuery();//update invoicedetail
                    cmdUpdateInvoiceDetail.Dispose();

                    result = true;
                    txn.Commit();
                }

                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }
        public bool Save(recRegistration domain, recSectorRegistration details, recUserSector userSector, recInvoice invoice, recInvoiceDetail invoiceDetail, recBilling billing)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strCommandMaster = "INSERT INTO transactions.recRegistration(PreDebtorName, NPWP, Address1, Address2, Address3, RegionID, CityID, ZipCode, Email, Phone, Fax, Website, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate, IsActive, IsIndividual) VALUES(@PreDebtorName, @NPWP, @Address1, @Address2, @Address3, @RegionID, @CityID, @ZipCode, @Email, @Phone, @Fax, @Website, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate, @IsActive, @IsIndividual)";
                    SqlCommand cmd = new SqlCommand(strCommandMaster, conn);
                    cmd.Parameters.Add("@PreDebtorName", SqlDbType.VarChar).Value = domain.PreDebtorName;
                    cmd.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = domain.NPWP;
                    cmd.Parameters.Add("@Address1", SqlDbType.VarChar).Value = (object)domain.Address1 ?? DBNull.Value;
                    cmd.Parameters.Add("@Address2", SqlDbType.VarChar).Value = (object)domain.Address2 ?? DBNull.Value;
                    cmd.Parameters.Add("@Address3", SqlDbType.VarChar).Value = (object)domain.Address3 ?? DBNull.Value;
                    cmd.Parameters.Add("@RegionID", SqlDbType.Int).Value = (object)domain.RegionID ?? DBNull.Value;
                    cmd.Parameters.Add("@CityID", SqlDbType.Int).Value = (object)domain.CityID ?? DBNull.Value;
                    cmd.Parameters.Add("@ZipCode", SqlDbType.VarChar).Value = (object)domain.ZipCode ?? DBNull.Value;
                    cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)domain.Email ?? DBNull.Value;
                    cmd.Parameters.Add("@Phone", SqlDbType.VarChar).Value = (object)domain.Phone ?? DBNull.Value;
                    cmd.Parameters.Add("@Fax", SqlDbType.VarChar).Value = (object)domain.Fax ?? DBNull.Value;
                    cmd.Parameters.Add("@Website", SqlDbType.VarChar).Value = (object)domain.Website ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domain.CreatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)domain.CreatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)domain.IsActive ?? DBNull.Value;
                    cmd.Parameters.Add("@IsIndividual", SqlDbType.Bit).Value = (object)domain.IsIndividual ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // Save Master
                    cmd.Dispose();

                    string strCommandGetId = " SELECT MAX(RegistrationId) RegistrationId FROM transactions.recRegistration WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetId = new SqlCommand(strCommandGetId, conn);
                    cmdGetId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;
                    cmdGetId.Transaction = txn;
                    var obj = cmdGetId.ExecuteScalar(); // Get Registration Id 
                    cmdGetId.Dispose();

                    decimal RegistrationId = obj == DBNull.Value ? 0m : Convert.ToDecimal(obj.ToString()); // Get Master Id

                    string strCommandDetail = "INSERT INTO transactions.recSectorRegistration(SectorID, SubSectorID, SubSubSectorID, IsApproved, ApprovedDate, ApprovedBy, InvoiceNo, InvoiceDate, InvoiceValue, PaymentNo, PaymentDate, PaymentValue, RegistrationStatusID, DebtorSectorID, RegistrationID, RegistrationDate, EmisionTypeID, EmisionValue, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, RegistrationEmisionTypeID) VALUES(@SectorID, @SubSectorID, @SubSubSectorID, @IsApproved, @ApprovedDate, @ApprovedBy, @InvoiceNo, @InvoiceDate, @InvoiceValue, @PaymentNo, @PaymentDate, @PaymentValue, @RegistrationStatusID, @DebtorSectorID, @RegistrationID, @RegistrationDate, @EmisionTypeID, @EmisionValue, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy, @RegistrationEmisionTypeID)";
                    SqlCommand cmdDetail = new SqlCommand(strCommandDetail, conn);
                    cmdDetail.Parameters.Add("@SectorID", SqlDbType.Int).Value = details.SectorID;
                    cmdDetail.Parameters.Add("@SubSectorID", SqlDbType.Int).Value = details.SubSectorID;
                    cmdDetail.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = details.SubSubSectorID;
                    cmdDetail.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = (object)details.IsApproved ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@ApprovedDate", SqlDbType.DateTime).Value = (object)details.ApprovedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@ApprovedBy", SqlDbType.VarChar).Value = (object)details.ApprovedBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)details.InvoiceNo ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = (object)details.InvoiceDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceValue", SqlDbType.Decimal).Value = (object)details.InvoiceValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)details.PaymentNo ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)details.PaymentDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)details.PaymentValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@RegistrationStatusID", SqlDbType.Int).Value = details.RegistrationStatusID;
                    cmdDetail.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)details.DebtorSectorID ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@RegistrationID", SqlDbType.Int).Value = Convert.ToInt32(RegistrationId);
                    cmdDetail.Parameters.Add("@RegistrationDate", SqlDbType.DateTime).Value = (object)details.RegistrationDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@EmisionTypeID", SqlDbType.SmallInt).Value = (object)details.EmisionTypeID ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@EmisionValue", SqlDbType.Money).Value = (object)details.EmisionValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)details.CreatedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)details.CreatedBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)details.UpdatedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)details.UpdatedBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@RegistrationEmisionTypeID", SqlDbType.SmallInt).Value = (object)details.RegistrationEmisionTypeID ?? DBNull.Value;

                    cmdDetail.Transaction = txn;
                    cmdDetail.ExecuteNonQuery(); // Save Detail
                    cmdDetail.Dispose();

                    string strCommandGetSectorRegistrationId = " SELECT MAX(SectorRegistrationID) SectorRegistrationID FROM transactions.recSectorRegistration WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetSectorRegistrationId = new SqlCommand(strCommandGetSectorRegistrationId, conn);
                    cmdGetSectorRegistrationId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = details.CreatedBy;
                    cmdGetSectorRegistrationId.Transaction = txn;
                    var objSectorRegistrationId = cmdGetSectorRegistrationId.ExecuteScalar(); // Get SectorRegistrationId
                    cmdGetSectorRegistrationId.Dispose();

                    decimal SectorRegistrationId = objSectorRegistrationId == DBNull.Value ? 0m : Convert.ToDecimal(objSectorRegistrationId.ToString()); // Get SectorRegistrationId

                    string strCommandUserSector = "INSERT INTO master.recUserSector(RegisterSectorID, DebtorSectorID, SubSubSectorID, UserTypeID, UserID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@RegisterSectorID, @DebtorSectorID, @SubSubSectorID, @UserTypeID, @UserID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdUserSector = new SqlCommand(strCommandUserSector, conn);
                    cmdUserSector.Parameters.Add("@RegisterSectorID", SqlDbType.Int).Value = Convert.ToInt32(SectorRegistrationId);
                    cmdUserSector.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)userSector.DebtorSectorID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = details.SubSubSectorID;
                    cmdUserSector.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)userSector.UserTypeID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UserID", SqlDbType.Int).Value = (object)userSector.UserID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)userSector.CreatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)userSector.CreatedBy ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)userSector.UpdatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)userSector.UpdatedBy ?? DBNull.Value;
                    cmdUserSector.Transaction = txn;
                    cmdUserSector.ExecuteNonQuery();
                    cmdUserSector.Dispose();

                    string strInvoice = "INSERT INTO transactions.recInvoice(InvoiceNo, InvoiceDate, InvoiceValue, BankID, BeSpelled, PaymentNo, PaymentDate, PaymentValue, IsHapusBuku, IsHapusTagih, DebtorSectorID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, Balance) VALUES(@InvoiceNo, @InvoiceDate, @InvoiceValue, @BankID, @BeSpelled, @PaymentNo, @PaymentDate, @PaymentValue, @IsHapusBuku, @IsHapusTagih, @DebtorSectorID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy, @Balance)";
                    SqlCommand cmdInvoice = new SqlCommand(strInvoice, conn);
                    cmdInvoice.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoice.InvoiceNo;
                    //cmdInvoice.Parameters.Add("@PeriodYear", SqlDbType.Int).Value = invoice.PeriodYear;
                    //cmdInvoice.Parameters.Add("@PeriodMonth", SqlDbType.SmallInt).Value = invoice.PeriodMonth;
                    //cmdInvoice.Parameters.Add("@TransDate", SqlDbType.DateTime).Value = invoice.TransDate;
                    cmdInvoice.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = invoice.InvoiceDate;
                    cmdInvoice.Parameters.Add("@InvoiceValue", SqlDbType.Money).Value = (object)invoice.InvoiceValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@BankID", SqlDbType.SmallInt).Value = invoice.BankID;
                    cmdInvoice.Parameters.Add("@BeSpelled", SqlDbType.VarChar).Value = (object)invoice.BeSpelled ?? DBNull.Value;
                    //cmdInvoice.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)invoice.ForPayment ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)invoice.PaymentNo ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)invoice.PaymentDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)invoice.PaymentValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@IsHapusBuku", SqlDbType.Bit).Value = (object)invoice.IsHapusBuku ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@IsHapusTagih", SqlDbType.Bit).Value = (object)invoice.IsHapusTagih ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)invoice.DebtorSectorID ?? -1;
                    //cmdInvoice.Parameters.Add("@PostingDate", SqlDbType.DateTime).Value = (object)invoice.PostingDate ?? DBNull.Value;
                    //cmdInvoice.Parameters.Add("@PostingBy", SqlDbType.VarChar).Value = (object)invoice.PostingBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)invoice.CreatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)invoice.CreatedBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)invoice.UpdatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoice.UpdatedBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@Balance", SqlDbType.Money).Value = (object)invoice.Balance ?? DBNull.Value;
                    cmdInvoice.Transaction = txn;
                    cmdInvoice.ExecuteNonQuery();//save invoice
                    cmdInvoice.Dispose();

                    string strInvoiceDetail = "INSERT INTO transactions.recInvoiceDetail(InvoiceNo, Value, TransTypeID, TariffID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, ForPayment, DueDate) VALUES(@InvoiceNo, @Value, @TransTypeID, @TariffID, @CreateDate, @CreateBy, @UpdateDate, @UpdateBy, @ForPayment, @DueDate)";
                    SqlCommand cmdInvoiceDetail = new SqlCommand(strInvoiceDetail, conn);
                    //cmdInvoiceDetail.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = invoiceDetail.InvoiceDetailID;
                    cmdInvoiceDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoiceDetail.InvoiceNo;
                    cmdInvoiceDetail.Parameters.Add("@Value", SqlDbType.Money).Value = invoiceDetail.Value;
                    cmdInvoiceDetail.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = invoiceDetail.TransTypeID;
                    cmdInvoiceDetail.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)invoiceDetail.TariffID ?? DBNull.Value;
                    //cmdInvoiceDetail.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = (object)invoiceDetail.CreatedDate ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = (object)invoiceDetail.CreatedBy ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)invoiceDetail.UpdatedDate ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)invoiceDetail.UpdatedBy ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)invoiceDetail.ForPayment ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = (object)invoiceDetail.DueDate ?? DBNull.Value;
                    cmdInvoiceDetail.Transaction = txn;
                    cmdInvoiceDetail.ExecuteNonQuery();//save invoice detail
                    cmdInvoiceDetail.Dispose();

                    string strGetInvoiceDetailId = " SELECT MAX(InvoiceDetailID) InvoiceDetailID FROM transactions.recInvoiceDetail WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetInvoiceDetailId = new SqlCommand(strGetInvoiceDetailId, conn);
                    cmdGetInvoiceDetailId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = invoiceDetail.CreatedBy;
                    cmdGetInvoiceDetailId.Transaction = txn;
                    var objInvoiceDetailId = cmdGetInvoiceDetailId.ExecuteScalar(); // Get SectorRegistrationId
                    cmdGetInvoiceDetailId.Dispose();

                    decimal InvoiceDetailId = objInvoiceDetailId == DBNull.Value ? 0m : Convert.ToDecimal(objInvoiceDetailId.ToString()); // Get InvoiceDetailId

                    string strBilling = "INSERT INTO transactions.recBilling(BillingNo, DebtorSectorID, TransTypeID, TariffID, Value, InvoiceNo, InvoiceDetailID, BillingDate,CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, DueDate, ForPayment, Balance) VALUES(@BillingNo, @DebtorSectorID, @TransTypeID, @TariffID, @Value, @InvoiceNo, @InvoiceDetailID, @BillingDate, @CreateDate, @CreateBy, @UpdateDate, @UpdateBy, @DueDate, @ForPayment, @Balance)";
                    SqlCommand cmdBilling = new SqlCommand(strBilling, conn);
                    cmdBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = billing.DebtorSectorID;
                    cmdBilling.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = billing.TransTypeID;
                    cmdBilling.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)billing.TariffID ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@Value", SqlDbType.Money).Value = billing.Value;
                    cmdBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)billing.InvoiceNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = (object)InvoiceDetailId ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@BillingDate", SqlDbType.DateTime).Value = (object)billing.BillingDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = (object)billing.CreatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = (object)billing.CreatedBy ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)billing.UpdatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)billing.UpdatedBy ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = (object)billing.DueDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)billing.ForPayment ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@Balance", SqlDbType.Money).Value = (object)billing.Balance ?? DBNull.Value;
                    cmdBilling.Transaction = txn;
                    cmdBilling.ExecuteNonQuery();//save billing
                    cmdBilling.Dispose();

                    string strUpdateInvoiceDetail = "UPDATE transactions.recInvoiceDetail SET BillingNo = @BillingNo ,UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy where InvoiceNo = @InvoiceNo ";
                    SqlCommand cmdUpdateInvoiceDetail = new SqlCommand(strUpdateInvoiceDetail, conn);
                    cmdUpdateInvoiceDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoiceDetail.InvoiceNo;
                    cmdUpdateInvoiceDetail.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdUpdateInvoiceDetail.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = DateTime.Now;
                    cmdUpdateInvoiceDetail.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoiceDetail.CreatedBy ?? DBNull.Value;
                    cmdUpdateInvoiceDetail.Transaction = txn;
                    cmdUpdateInvoiceDetail.ExecuteNonQuery();//update invoicedetail
                    cmdUpdateInvoiceDetail.Dispose();

                    result = true;
                    txn.Commit();
                }

                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }
        public bool SetNonActive(int key)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "UPDATE transactions.recRegistration set IsActive = 0 WHERE RegistrationID = @RegistrationID";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@RegistrationID", SqlDbType.Int).Value = key;

                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        result = true;
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return result;
        }
        public recRegistration GetByIdEditItem(int key)
        {
            recRegistration t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                StringBuilder sbQuery = new StringBuilder();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    sbQuery.AppendFormat("select mru.UserCode,mru.Password,trr.PreDebtorName,trr.NPWP,trsr.SectorID,trsr.SubSectorID,trsr.SubSubSectorID,{0}", Environment.NewLine);
                    sbQuery.AppendFormat("trr.Address1,trr.Address2,trr.Address3,trr.ZipCode,trr.Phone,trr.Fax,trr.Email,trr.Website,{0}", Environment.NewLine);
                    sbQuery.AppendFormat("trsr.EmisionValue,trsr.EmisionTypeID,trr.RegionID,trr.CityID,trr.IsActive {0}", Environment.NewLine);
                    sbQuery.AppendFormat("from transactions.recRegistration trr {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join transactions.recSectorRegistration trsr on trsr.RegistrationID=trr.RegistrationID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recSector mrs on mrs.SectorID=trsr.SectorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recSubSector mrss on mrss.SubSectorID=trsr.SubSectorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recSubSubSector mrsss on mrsss.SubSubSectorID=trsr.SubSubSectorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("left join master.recRegion mrr on mrr.RegionID=trr.RegionID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("left join master.recCity mrc on mrc.CityID=trr.CityID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("left join master.recEmisionType mret on mret.EmisionTypeID=trsr.EmisionTypeID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recUserSector mrus on mrus.RegisterSectorID=trsr.SectorRegistrationID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recUser mru on mru.UserID=mrus.UserID WHERE trsr.SectorRegistrationID=@SectorRegistrationID");
                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@SectorRegistrationID", SqlDbType.Int).Value = key;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recRegistration(true);
                                t.PreDebtorName = dr[recRegistration.Fields.PreDebtorName] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.PreDebtorName].ToString();
                                t.NPWP = dr[recRegistration.Fields.NPWP] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.NPWP].ToString();
                                t.Address1 = dr[recRegistration.Fields.Address1] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Address1].ToString();
                                t.Address2 = dr[recRegistration.Fields.Address2] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Address2].ToString();
                                t.Address3 = dr[recRegistration.Fields.Address3] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Address3].ToString();
                                t.RegionID = dr[recRegistration.Fields.RegionID] == DBNull.Value ? (int?)null : int.Parse(dr[recRegistration.Fields.RegionID].ToString());
                                t.CityID = dr[recRegistration.Fields.CityID] == DBNull.Value ? (int?)null : int.Parse(dr[recRegistration.Fields.CityID].ToString());
                                t.ZipCode = dr[recRegistration.Fields.ZipCode] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.ZipCode].ToString();
                                t.Email = dr[recRegistration.Fields.Email] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Email].ToString();
                                t.Phone = dr[recRegistration.Fields.Phone] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Phone].ToString();
                                t.Fax = dr[recRegistration.Fields.Fax] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Fax].ToString();
                                t.Website = dr[recRegistration.Fields.Website] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Website].ToString();
                                t.IsActive = dr[recRegistration.Fields.IsActive] == DBNull.Value ? (bool?)null : bool.Parse(dr[recRegistration.Fields.IsActive].ToString());
                                t.recSectorRegistration.SectorID = dr[recSectorRegistration.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr[recSectorRegistration.Fields.SectorID].ToString());
                                t.recSectorRegistration.SubSectorID = dr[recSectorRegistration.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recSectorRegistration.Fields.SubSectorID].ToString());
                                t.recSectorRegistration.SubSubSectorID = dr[recSectorRegistration.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recSectorRegistration.Fields.SubSubSectorID].ToString());
                                t.recSectorRegistration.EmisionValue = dr[recSectorRegistration.Fields.EmisionValue] == DBNull.Value ? 0 : Convert.ToDecimal(dr[recSectorRegistration.Fields.EmisionValue].ToString());
                                t.recSectorRegistration.EmisionTypeID = dr[recSectorRegistration.Fields.EmisionTypeID] == DBNull.Value ? (short?)null : short.Parse(dr[recSectorRegistration.Fields.EmisionTypeID].ToString());
                                t.recUser.UserCode = dr[recUser.Fields.UserCode] == DBNull.Value ? string.Empty : dr[recUser.Fields.UserCode].ToString();
                                t.recUser.Password = dr[recUser.Fields.Password] == DBNull.Value ? string.Empty : dr[recUser.Fields.Password].ToString();


                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }
        public bool Update(recSectorRegistration sectorRegistration, recInvoice invoice, recUserSector userSector, recPayment payment)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strUpdateSectorRegistration = "UPDATE transactions.recSectorRegistration SET  IsApproved = @IsApproved, ApprovedDate = @ApprovedDate, ApprovedBy = @ApprovedBy, PaymentNo = @PaymentNo, PaymentDate = @PaymentDate, PaymentValue = @PaymentValue, RegistrationStatusID = @RegistrationStatusID, DebtorSectorID = @DebtorSectorID, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE SectorRegistrationID = @SectorRegistrationID";
                    SqlCommand cmd = new SqlCommand(strUpdateSectorRegistration, conn);
                    cmd.Parameters.Add("@SectorRegistrationID", SqlDbType.Int).Value = sectorRegistration.SectorRegistrationID;
                    cmd.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = (object)sectorRegistration.IsApproved ?? DBNull.Value;
                    cmd.Parameters.Add("@ApprovedDate", SqlDbType.DateTime).Value = (object)sectorRegistration.ApprovedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@ApprovedBy", SqlDbType.VarChar).Value = (object)sectorRegistration.ApprovedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)sectorRegistration.PaymentNo ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)sectorRegistration.PaymentDate ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)sectorRegistration.PaymentValue ?? DBNull.Value;
                    cmd.Parameters.Add("@RegistrationStatusID", SqlDbType.Int).Value = sectorRegistration.RegistrationStatusID;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)sectorRegistration.DebtorSectorID ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)sectorRegistration.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)sectorRegistration.UpdatedBy ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();// update sector registration 
                    cmd.Dispose();

                    string strInvoice = "UPDATE transactions.recInvoice SET PaymentNo = @PaymentNo, PaymentDate = @PaymentDate, PaymentValue = @PaymentValue, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE InvoiceNo = @InvoiceNo";
                    SqlCommand cmdInvoice = new SqlCommand(strInvoice, conn);
                    cmdInvoice.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)invoice.InvoiceNo ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)invoice.PaymentNo ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)invoice.PaymentDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)invoice.PaymentValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)invoice.UpdatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoice.UpdatedBy ?? DBNull.Value;
                    cmdInvoice.Transaction = txn;
                    cmdInvoice.ExecuteNonQuery();// update invoice
                    cmdInvoice.Dispose();

                    string strUserSector = "UPDATE master.recUserSector SET UserTypeID = @UserTypeID, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE RegisterSectorID = @RegisterSectorID";
                    SqlCommand cmdUserSector = new SqlCommand(strUserSector, conn);
                    cmdUserSector.Parameters.Add("@RegisterSectorID", SqlDbType.Int).Value = (object)userSector.RegisterSectorID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)userSector.UserTypeID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)userSector.UpdatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)userSector.UpdatedBy ?? DBNull.Value;
                    cmdUserSector.Transaction = txn;
                    cmdUserSector.ExecuteNonQuery();// update user sector
                    cmdUserSector.Dispose();

                    string strPayment = "INSERT INTO transactions.recPayment(TransDate, PaymentNo, PaymentDate, PaymentValue, InvoiceNo, PostingDate, PostingBy, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@TransDate, @PaymentNo, @PaymentDate, @PaymentValue, @InvoiceNo, @PostingDate, @PostingBy, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdPayment = new SqlCommand(strPayment, conn);
                    cmdPayment.Parameters.Add("@TransDate", SqlDbType.DateTime).Value = payment.TransDate;
                    cmdPayment.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = payment.PaymentNo;
                    cmdPayment.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = payment.PaymentDate;
                    cmdPayment.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = payment.PaymentValue;
                    cmdPayment.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = payment.InvoiceNo;
                    cmdPayment.Parameters.Add("@PostingDate", SqlDbType.DateTime).Value = (object)payment.PostingDate ?? DBNull.Value;
                    cmdPayment.Parameters.Add("@PostingBy", SqlDbType.VarChar).Value = (object)payment.PostingBy ?? DBNull.Value;
                    cmdPayment.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)payment.CreatedDate ?? DBNull.Value;
                    cmdPayment.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)payment.CreatedBy ?? DBNull.Value;
                    cmdPayment.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)payment.UpdatedDate ?? DBNull.Value;
                    cmdPayment.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)payment.UpdatedBy ?? DBNull.Value;
                    cmdPayment.Transaction = txn;
                    cmdPayment.ExecuteNonQuery();// save payment
                    cmdPayment.Dispose();

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }
        public bool Update(recSectorRegistration sectorRegistration, recInvoice invoice, recUserSector userSector)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strUpdateSectorRegistration = "UPDATE transactions.recSectorRegistration SET  IsApproved = @IsApproved, ApprovedDate = @ApprovedDate, ApprovedBy = @ApprovedBy, PaymentNo = @PaymentNo, PaymentDate = @PaymentDate, PaymentValue = @PaymentValue, RegistrationStatusID = @RegistrationStatusID, DebtorSectorID = @DebtorSectorID, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE SectorRegistrationID = @SectorRegistrationID";
                    SqlCommand cmd = new SqlCommand(strUpdateSectorRegistration, conn);
                    cmd.Parameters.Add("@SectorRegistrationID", SqlDbType.Int).Value = sectorRegistration.SectorRegistrationID;
                    cmd.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = (object)sectorRegistration.IsApproved ?? DBNull.Value;
                    cmd.Parameters.Add("@ApprovedDate", SqlDbType.DateTime).Value = (object)sectorRegistration.ApprovedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@ApprovedBy", SqlDbType.VarChar).Value = (object)sectorRegistration.ApprovedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)sectorRegistration.PaymentNo ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)sectorRegistration.PaymentDate ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)sectorRegistration.PaymentValue ?? DBNull.Value;
                    cmd.Parameters.Add("@RegistrationStatusID", SqlDbType.Int).Value = sectorRegistration.RegistrationStatusID;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)sectorRegistration.DebtorSectorID ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)sectorRegistration.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)sectorRegistration.UpdatedBy ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();// update sector registration 
                    cmd.Dispose();

                    string strInvoice = "UPDATE transactions.recInvoice SET PaymentNo = @PaymentNo, PaymentDate = @PaymentDate, PaymentValue = @PaymentValue, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE InvoiceNo = @InvoiceNo";
                    SqlCommand cmdInvoice = new SqlCommand(strInvoice, conn);
                    cmdInvoice.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)invoice.InvoiceNo ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)invoice.PaymentNo ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)invoice.PaymentDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)invoice.PaymentValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)invoice.UpdatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoice.UpdatedBy ?? DBNull.Value;
                    cmdInvoice.Transaction = txn;
                    cmdInvoice.ExecuteNonQuery();// update invoice
                    cmdInvoice.Dispose();

                    string strUserSector = "UPDATE master.recUserSector SET UserTypeID = @UserTypeID, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE RegisterSectorID = @RegisterSectorID";
                    SqlCommand cmdUserSector = new SqlCommand(strUserSector, conn);
                    cmdUserSector.Parameters.Add("@RegisterSectorID", SqlDbType.Int).Value = (object)userSector.RegisterSectorID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)userSector.UserTypeID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)userSector.UpdatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)userSector.UpdatedBy ?? DBNull.Value;
                    cmdUserSector.Transaction = txn;
                    cmdUserSector.ExecuteNonQuery();// update user sector
                    cmdUserSector.Dispose();

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }
        public bool Update(recDebtor debtor, recDebtorSector debtorSector, recSectorRegistration sectorRegistration, recInvoice invoice, recUserSector userSector, recBilling billing)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strdebtor = "INSERT INTO master.recDebtor(DebtorCode, DebtorName, NPWP, Address1, Address2, Address3, ZipCode, RegionID, CityID, Email, Phone, Fax, Website, IsActive, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, IsIndividual) VALUES(@DebtorCode, @DebtorName, @NPWP, @Address1, @Address2, @Address3, @ZipCode, @RegionID, @CityID, @Email, @Phone, @Fax, @Website, @IsActive, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy, @IsIndividual)";
                    SqlCommand cmdDebtor = new SqlCommand(strdebtor, conn);
                    cmdDebtor.Parameters.Add("@DebtorCode", SqlDbType.VarChar).Value = debtor.DebtorCode;
                    cmdDebtor.Parameters.Add("@DebtorName", SqlDbType.VarChar).Value = debtor.DebtorName;
                    cmdDebtor.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = debtor.NPWP;
                    cmdDebtor.Parameters.Add("@Address1", SqlDbType.VarChar).Value = (object)debtor.Address1 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Address2", SqlDbType.VarChar).Value = (object)debtor.Address2 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Address3", SqlDbType.VarChar).Value = (object)debtor.Address3 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@ZipCode", SqlDbType.VarChar).Value = (object)debtor.ZipCode ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@RegionID", SqlDbType.Int).Value = (object)debtor.RegionID ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@CityID", SqlDbType.Int).Value = (object)debtor.CityID ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)debtor.Email ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Phone", SqlDbType.VarChar).Value = (object)debtor.Phone ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Fax", SqlDbType.VarChar).Value = (object)debtor.Fax ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Website", SqlDbType.VarChar).Value = (object)debtor.Website ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@IsActive", SqlDbType.Bit).Value = debtor.IsActive;
                    cmdDebtor.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)debtor.CreatedDate ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)debtor.CreatedBy ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)debtor.UpdatedDate ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)debtor.UpdatedBy ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@IsIndividual", SqlDbType.Bit).Value = (object)debtor.IsIndividual ?? DBNull.Value;
                    cmdDebtor.Transaction = txn;
                    cmdDebtor.ExecuteNonQuery();//save debtor
                    cmdDebtor.Dispose();

                    string strCommandGetDebtorId = " SELECT MAX(DebtorID) DebtorID FROM master.recDebtor WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetDebtorId = new SqlCommand(strCommandGetDebtorId, conn);
                    cmdGetDebtorId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = debtor.CreatedBy;
                    cmdGetDebtorId.Transaction = txn;
                    var obj = cmdGetDebtorId.ExecuteScalar(); // Get Debtor Id 
                    cmdGetDebtorId.Dispose();

                    decimal debtorId = obj == DBNull.Value ? 0m : Convert.ToDecimal(obj.ToString()); // Debtor Id

                    string strDebtorSector = "INSERT INTO master.recDebtorSector(DebtorID, SectorID, SubSectorID, SubSubSectorID, IsActive, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@DebtorID, @SectorID, @SubSectorID, @SubSubSectorID, @IsActive, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdDebtorSector = new SqlCommand(strDebtorSector, conn);
                    cmdDebtorSector.Parameters.Add("@DebtorID", SqlDbType.Int).Value = Convert.ToInt32(debtorId);
                    cmdDebtorSector.Parameters.Add("@SectorID", SqlDbType.Int).Value = debtorSector.SectorID;
                    cmdDebtorSector.Parameters.Add("@SubSectorID", SqlDbType.Int).Value = debtorSector.SubSectorID;
                    cmdDebtorSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = debtorSector.SubSubSectorID;
                    cmdDebtorSector.Parameters.Add("@IsActive", SqlDbType.Bit).Value = debtorSector.IsActive;
                    cmdDebtorSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)debtorSector.CreatedDate ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)debtorSector.CreatedBy ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)debtorSector.UpdatedDate ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)debtorSector.UpdatedBy ?? DBNull.Value;
                    cmdDebtorSector.Transaction = txn;
                    cmdDebtorSector.ExecuteNonQuery();//save debtorsector
                    cmdDebtorSector.Dispose();

                    string strCommandGetDebtorSectorId = " SELECT MAX(DebtorSectorID) DebtorSectorID FROM master.recDebtorSector WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetDebtorSectorId = new SqlCommand(strCommandGetDebtorSectorId, conn);
                    cmdGetDebtorSectorId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = debtorSector.CreatedBy;
                    cmdGetDebtorSectorId.Transaction = txn;
                    var objDectorSector = cmdGetDebtorSectorId.ExecuteScalar(); // Get DebtorSectorId 
                    cmdGetDebtorSectorId.Dispose();

                    decimal debtorSectorId = objDectorSector == DBNull.Value ? 0m : Convert.ToDecimal(objDectorSector.ToString()); // DebtorSectorId

                    string strUpdateSectorRegistration = "UPDATE transactions.recSectorRegistration SET  IsApproved = @IsApproved, ApprovedDate = @ApprovedDate, ApprovedBy = @ApprovedBy, PaymentNo = @PaymentNo, PaymentDate = @PaymentDate, PaymentValue = @PaymentValue, RegistrationStatusID = @RegistrationStatusID, DebtorSectorID = @DebtorSectorID, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE SectorRegistrationID = @SectorRegistrationID";
                    SqlCommand cmd = new SqlCommand(strUpdateSectorRegistration, conn);
                    cmd.Parameters.Add("@SectorRegistrationID", SqlDbType.Int).Value = sectorRegistration.SectorRegistrationID;
                    cmd.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = (object)sectorRegistration.IsApproved ?? DBNull.Value;
                    cmd.Parameters.Add("@ApprovedDate", SqlDbType.DateTime).Value = (object)sectorRegistration.ApprovedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@ApprovedBy", SqlDbType.VarChar).Value = (object)sectorRegistration.ApprovedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)sectorRegistration.PaymentNo ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)sectorRegistration.PaymentDate ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)sectorRegistration.PaymentValue ?? DBNull.Value;
                    cmd.Parameters.Add("@RegistrationStatusID", SqlDbType.Int).Value = sectorRegistration.RegistrationStatusID;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)Convert.ToInt32(debtorSectorId) ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)sectorRegistration.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)sectorRegistration.UpdatedBy ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();// update sector registration 
                    cmd.Dispose();

                    string strInvoice = "UPDATE transactions.recInvoice SET PaymentNo = @PaymentNo, PaymentDate = @PaymentDate, PaymentValue = @PaymentValue, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy, DebtorSectorID = @DebtorSectorID WHERE InvoiceNo = @InvoiceNo";
                    SqlCommand cmdInvoice = new SqlCommand(strInvoice, conn);
                    cmdInvoice.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)invoice.InvoiceNo ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)invoice.PaymentNo ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)invoice.PaymentDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)invoice.PaymentValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)invoice.UpdatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoice.UpdatedBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)Convert.ToInt32(debtorSectorId) ?? DBNull.Value;
                    cmdInvoice.Transaction = txn;
                    cmdInvoice.ExecuteNonQuery();// update invoice
                    cmdInvoice.Dispose();

                    string strUserSector = "UPDATE master.recUserSector SET UserTypeID = @UserTypeID, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy, DebtorSectorID = @DebtorSectorID WHERE RegisterSectorID = @RegisterSectorID";
                    SqlCommand cmdUserSector = new SqlCommand(strUserSector, conn);
                    cmdUserSector.Parameters.Add("@RegisterSectorID", SqlDbType.Int).Value = (object)userSector.RegisterSectorID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)userSector.UserTypeID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)userSector.UpdatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)userSector.UpdatedBy ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)Convert.ToInt32(debtorSectorId) ?? DBNull.Value;
                    cmdUserSector.Transaction = txn;
                    cmdUserSector.ExecuteNonQuery();// update user sector
                    cmdUserSector.Dispose();

                    string strBilling = "UPDATE transactions.recBilling SET DebtorSectorID = @DebtorSectorID, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE InvoiceNo = @InvoiceNo";
                    SqlCommand cmdBilling = new SqlCommand(strBilling, conn);
                    cmdBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)billing.InvoiceNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)Convert.ToInt32(debtorSectorId) ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)billing.UpdatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)billing.UpdatedBy ?? DBNull.Value;
                    cmdBilling.Transaction = txn;
                    cmdBilling.ExecuteNonQuery();// update billing
                    cmdBilling.Dispose();

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }
        public bool Update(recDebtor debtor, recDebtorSector debtorSector, recSectorRegistration sectorRegistration, recInvoice invoice, recUserSector userSector, recBilling billing, recUser user, recUserRole userrole)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strdebtor = "INSERT INTO master.recDebtor(DebtorCode, DebtorName, NPWP, Address1, Address2, Address3, ZipCode, RegionID, CityID, Email, Phone, Fax, Website, IsActive, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, IsIndividual) VALUES(@DebtorCode, @DebtorName, @NPWP, @Address1, @Address2, @Address3, @ZipCode, @RegionID, @CityID, @Email, @Phone, @Fax, @Website, @IsActive, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy, @IsIndividual)";
                    SqlCommand cmdDebtor = new SqlCommand(strdebtor, conn);
                    cmdDebtor.Parameters.Add("@DebtorCode", SqlDbType.VarChar).Value = debtor.DebtorCode;
                    cmdDebtor.Parameters.Add("@DebtorName", SqlDbType.VarChar).Value = debtor.DebtorName;
                    cmdDebtor.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = debtor.NPWP;
                    cmdDebtor.Parameters.Add("@Address1", SqlDbType.VarChar).Value = (object)debtor.Address1 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Address2", SqlDbType.VarChar).Value = (object)debtor.Address2 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Address3", SqlDbType.VarChar).Value = (object)debtor.Address3 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@ZipCode", SqlDbType.VarChar).Value = (object)debtor.ZipCode ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@RegionID", SqlDbType.Int).Value = (object)debtor.RegionID ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@CityID", SqlDbType.Int).Value = (object)debtor.CityID ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)debtor.Email ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Phone", SqlDbType.VarChar).Value = (object)debtor.Phone ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Fax", SqlDbType.VarChar).Value = (object)debtor.Fax ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Website", SqlDbType.VarChar).Value = (object)debtor.Website ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@IsActive", SqlDbType.Bit).Value = debtor.IsActive;
                    cmdDebtor.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)debtor.CreatedDate ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)debtor.CreatedBy ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)debtor.UpdatedDate ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)debtor.UpdatedBy ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@IsIndividual", SqlDbType.Bit).Value = (object)debtor.IsIndividual ?? DBNull.Value;
                    cmdDebtor.Transaction = txn;
                    cmdDebtor.ExecuteNonQuery();//save debtor
                    cmdDebtor.Dispose();

                    string strCommandGetDebtorId = " SELECT MAX(DebtorID) DebtorID FROM master.recDebtor WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetDebtorId = new SqlCommand(strCommandGetDebtorId, conn);
                    cmdGetDebtorId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = debtor.CreatedBy;
                    cmdGetDebtorId.Transaction = txn;
                    var obj = cmdGetDebtorId.ExecuteScalar(); // Get Debtor Id 
                    cmdGetDebtorId.Dispose();

                    decimal debtorId = obj == DBNull.Value ? 0m : Convert.ToDecimal(obj.ToString()); // Debtor Id

                    string strDebtorSector = "INSERT INTO master.recDebtorSector(DebtorID, SectorID, SubSectorID, SubSubSectorID, IsActive, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@DebtorID, @SectorID, @SubSectorID, @SubSubSectorID, @IsActive, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdDebtorSector = new SqlCommand(strDebtorSector, conn);
                    cmdDebtorSector.Parameters.Add("@DebtorID", SqlDbType.Int).Value = Convert.ToInt32(debtorId);
                    cmdDebtorSector.Parameters.Add("@SectorID", SqlDbType.Int).Value = debtorSector.SectorID;
                    cmdDebtorSector.Parameters.Add("@SubSectorID", SqlDbType.Int).Value = debtorSector.SubSectorID;
                    cmdDebtorSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = debtorSector.SubSubSectorID;
                    cmdDebtorSector.Parameters.Add("@IsActive", SqlDbType.Bit).Value = debtorSector.IsActive;
                    cmdDebtorSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)debtorSector.CreatedDate ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)debtorSector.CreatedBy ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)debtorSector.UpdatedDate ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)debtorSector.UpdatedBy ?? DBNull.Value;
                    cmdDebtorSector.Transaction = txn;
                    cmdDebtorSector.ExecuteNonQuery();//save debtorsector
                    cmdDebtorSector.Dispose();

                    string strCommandGetDebtorSectorId = " SELECT MAX(DebtorSectorID) DebtorSectorID FROM master.recDebtorSector WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetDebtorSectorId = new SqlCommand(strCommandGetDebtorSectorId, conn);
                    cmdGetDebtorSectorId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = debtorSector.CreatedBy;
                    cmdGetDebtorSectorId.Transaction = txn;
                    var objDectorSector = cmdGetDebtorSectorId.ExecuteScalar(); // Get DebtorSectorId 
                    cmdGetDebtorSectorId.Dispose();

                    decimal debtorSectorId = objDectorSector == DBNull.Value ? 0m : Convert.ToDecimal(objDectorSector.ToString()); // DebtorSectorId

                    string strUpdateSectorRegistration = "UPDATE transactions.recSectorRegistration SET  IsApproved = @IsApproved, ApprovedDate = @ApprovedDate, ApprovedBy = @ApprovedBy, PaymentNo = @PaymentNo, PaymentDate = @PaymentDate, PaymentValue = @PaymentValue, RegistrationStatusID = @RegistrationStatusID, DebtorSectorID = @DebtorSectorID, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE SectorRegistrationID = @SectorRegistrationID";
                    SqlCommand cmd = new SqlCommand(strUpdateSectorRegistration, conn);
                    cmd.Parameters.Add("@SectorRegistrationID", SqlDbType.Int).Value = sectorRegistration.SectorRegistrationID;
                    cmd.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = (object)sectorRegistration.IsApproved ?? DBNull.Value;
                    cmd.Parameters.Add("@ApprovedDate", SqlDbType.DateTime).Value = (object)sectorRegistration.ApprovedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@ApprovedBy", SqlDbType.VarChar).Value = (object)sectorRegistration.ApprovedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)sectorRegistration.PaymentNo ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)sectorRegistration.PaymentDate ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)sectorRegistration.PaymentValue ?? DBNull.Value;
                    cmd.Parameters.Add("@RegistrationStatusID", SqlDbType.Int).Value = sectorRegistration.RegistrationStatusID;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)Convert.ToInt32(debtorSectorId) ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)sectorRegistration.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)sectorRegistration.UpdatedBy ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();// update sector registration 
                    cmd.Dispose();

                    string strInvoice = "UPDATE transactions.recInvoice SET PaymentNo = @PaymentNo, PaymentDate = @PaymentDate, PaymentValue = @PaymentValue, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy, DebtorSectorID = @DebtorSectorID, Balance = @Balance WHERE InvoiceNo = @InvoiceNo";
                    SqlCommand cmdInvoice = new SqlCommand(strInvoice, conn);
                    cmdInvoice.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)invoice.InvoiceNo ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)invoice.PaymentNo ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)invoice.PaymentDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)invoice.PaymentValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)invoice.UpdatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoice.UpdatedBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)Convert.ToInt32(debtorSectorId) ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@Balance", SqlDbType.Decimal).Value = (object)invoice.Balance ?? DBNull.Value;
                    cmdInvoice.Transaction = txn;
                    cmdInvoice.ExecuteNonQuery();// update invoice
                    cmdInvoice.Dispose();

                    string strUserSector = "UPDATE master.recUserSector SET UserTypeID = @UserTypeID, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy, DebtorSectorID = @DebtorSectorID WHERE RegisterSectorID = @RegisterSectorID";
                    SqlCommand cmdUserSector = new SqlCommand(strUserSector, conn);
                    cmdUserSector.Parameters.Add("@RegisterSectorID", SqlDbType.Int).Value = (object)userSector.RegisterSectorID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)userSector.UserTypeID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)userSector.UpdatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)userSector.UpdatedBy ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)Convert.ToInt32(debtorSectorId) ?? DBNull.Value;
                    cmdUserSector.Transaction = txn;
                    cmdUserSector.ExecuteNonQuery();// update user sector
                    cmdUserSector.Dispose();

                    string strBilling = "UPDATE transactions.recBilling SET DebtorSectorID = @DebtorSectorID, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy, Balance = @Balance WHERE InvoiceNo = @InvoiceNo";
                    SqlCommand cmdBilling = new SqlCommand(strBilling, conn);
                    cmdBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)billing.InvoiceNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)Convert.ToInt32(debtorSectorId) ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)billing.UpdatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)billing.UpdatedBy ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@Balance", SqlDbType.Decimal).Value = (object)billing.Balance ?? DBNull.Value;
                    cmdBilling.Transaction = txn;
                    cmdBilling.ExecuteNonQuery();// update billing
                    cmdBilling.Dispose();

                    string strUser = "UPDATE master.recUser SET DebtorID = @DebtorID, UpdatedBy = @UpdatedBy, UpdatedDate = @UpdatedDate, UserTypeID = @UserTypeID WHERE UserID = @UserID";
                    SqlCommand cmdUser = new SqlCommand(strUser, conn);
                    cmdUser.Parameters.Add("@UserID", SqlDbType.Int).Value = user.UserID;
                    cmdUser.Parameters.Add("@DebtorID", SqlDbType.Int).Value = Convert.ToInt32(debtorId);
                    cmdUser.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)user.UpdatedBy ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)user.UpdatedDate ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)user.UserTypeID ?? DBNull.Value;
                    cmdUser.Transaction = txn;
                    cmdUser.ExecuteNonQuery();// update user
                    cmdUser.Dispose();

                    string strUserRole = "UPDATE master.recUserRole SET RoleID = @RoleID WHERE UserID = @UserID";
                    SqlCommand cmdUserRole = new SqlCommand(strUserRole, conn);
                    cmdUserRole.Parameters.Add("@UserID", SqlDbType.Int).Value = (object)userrole.UserID ?? DBNull.Value;
                    cmdUserRole.Parameters.Add("@RoleID", SqlDbType.Int).Value = (object)userrole.RoleID ?? DBNull.Value;
                    cmdUserRole.Transaction = txn;
                    cmdUserRole.ExecuteNonQuery();// update user role menjadi debitur
                    cmdUserRole.Dispose();

                    string strPayment = "UPDATE transactions.recPayment SET DebtorSectorID = @DebtorSectorID WHERE InvoiceNo = @InvoiceNo";
                    SqlCommand cmdPayment = new SqlCommand(strPayment, conn);
                    cmdPayment.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)billing.InvoiceNo ?? DBNull.Value;
                    cmdPayment.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)Convert.ToInt32(debtorSectorId) ?? DBNull.Value;

                    cmdPayment.Transaction = txn;
                    cmdPayment.ExecuteNonQuery();// update Payment
                    cmdPayment.Dispose();

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }
        public bool Update(recDebtorSector debtorSector, recSectorRegistration sectorRegistration, recInvoice invoice, recUserSector userSector, recBilling billing, recUser user, recUserRole userrole)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strDebtorSector = "INSERT INTO master.recDebtorSector(DebtorID, SectorID, SubSectorID, SubSubSectorID, IsActive, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@DebtorID, @SectorID, @SubSectorID, @SubSubSectorID, @IsActive, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdDebtorSector = new SqlCommand(strDebtorSector, conn);
                    cmdDebtorSector.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtorSector.DebtorID;
                    cmdDebtorSector.Parameters.Add("@SectorID", SqlDbType.Int).Value = debtorSector.SectorID;
                    cmdDebtorSector.Parameters.Add("@SubSectorID", SqlDbType.Int).Value = debtorSector.SubSectorID;
                    cmdDebtorSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = debtorSector.SubSubSectorID;
                    cmdDebtorSector.Parameters.Add("@IsActive", SqlDbType.Bit).Value = debtorSector.IsActive;
                    cmdDebtorSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)debtorSector.CreatedDate ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)debtorSector.CreatedBy ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)debtorSector.UpdatedDate ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)debtorSector.UpdatedBy ?? DBNull.Value;
                    cmdDebtorSector.Transaction = txn;
                    cmdDebtorSector.ExecuteNonQuery();//save debtorsector
                    cmdDebtorSector.Dispose();

                    string strCommandGetDebtorSectorId = " SELECT MAX(DebtorSectorID) DebtorSectorID FROM master.recDebtorSector WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetDebtorSectorId = new SqlCommand(strCommandGetDebtorSectorId, conn);
                    cmdGetDebtorSectorId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = debtorSector.CreatedBy;
                    cmdGetDebtorSectorId.Transaction = txn;
                    var objDectorSector = cmdGetDebtorSectorId.ExecuteScalar(); // Get DebtorSectorId 
                    cmdGetDebtorSectorId.Dispose();

                    decimal debtorSectorId = objDectorSector == DBNull.Value ? 0m : Convert.ToDecimal(objDectorSector.ToString()); // DebtorSectorId

                    string strUpdateSectorRegistration = "UPDATE transactions.recSectorRegistration SET  IsApproved = @IsApproved, ApprovedDate = @ApprovedDate, ApprovedBy = @ApprovedBy, PaymentNo = @PaymentNo, PaymentDate = @PaymentDate, PaymentValue = @PaymentValue, RegistrationStatusID = @RegistrationStatusID, DebtorSectorID = @DebtorSectorID, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE SectorRegistrationID = @SectorRegistrationID";
                    SqlCommand cmd = new SqlCommand(strUpdateSectorRegistration, conn);
                    cmd.Parameters.Add("@SectorRegistrationID", SqlDbType.Int).Value = sectorRegistration.SectorRegistrationID;
                    cmd.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = (object)sectorRegistration.IsApproved ?? DBNull.Value;
                    cmd.Parameters.Add("@ApprovedDate", SqlDbType.DateTime).Value = (object)sectorRegistration.ApprovedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@ApprovedBy", SqlDbType.VarChar).Value = (object)sectorRegistration.ApprovedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)sectorRegistration.PaymentNo ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)sectorRegistration.PaymentDate ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)sectorRegistration.PaymentValue ?? DBNull.Value;
                    cmd.Parameters.Add("@RegistrationStatusID", SqlDbType.Int).Value = sectorRegistration.RegistrationStatusID;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)Convert.ToInt32(debtorSectorId) ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)sectorRegistration.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)sectorRegistration.UpdatedBy ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();// update sector registration 
                    cmd.Dispose();

                    string strInvoice = "UPDATE transactions.recInvoice SET PaymentNo = @PaymentNo, PaymentDate = @PaymentDate, PaymentValue = @PaymentValue, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy, DebtorSectorID = @DebtorSectorID, Balance = @Balance WHERE InvoiceNo = @InvoiceNo";
                    SqlCommand cmdInvoice = new SqlCommand(strInvoice, conn);
                    cmdInvoice.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)invoice.InvoiceNo ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)invoice.PaymentNo ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)invoice.PaymentDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)invoice.PaymentValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)invoice.UpdatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoice.UpdatedBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)Convert.ToInt32(debtorSectorId) ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@Balance", SqlDbType.Decimal).Value = (object)invoice.Balance ?? DBNull.Value;
                    cmdInvoice.Transaction = txn;
                    cmdInvoice.ExecuteNonQuery();// update invoice
                    cmdInvoice.Dispose();

                    string strUserSector = "UPDATE master.recUserSector SET UserTypeID = @UserTypeID, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy, DebtorSectorID = @DebtorSectorID WHERE RegisterSectorID = @RegisterSectorID";
                    SqlCommand cmdUserSector = new SqlCommand(strUserSector, conn);
                    cmdUserSector.Parameters.Add("@RegisterSectorID", SqlDbType.Int).Value = (object)userSector.RegisterSectorID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)userSector.UserTypeID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)userSector.UpdatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)userSector.UpdatedBy ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)Convert.ToInt32(debtorSectorId) ?? DBNull.Value;
                    cmdUserSector.Transaction = txn;
                    cmdUserSector.ExecuteNonQuery();// update user sector
                    cmdUserSector.Dispose();

                    string strBilling = "UPDATE transactions.recBilling SET DebtorSectorID = @DebtorSectorID, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy, Balance = @Balance WHERE InvoiceNo = @InvoiceNo";
                    SqlCommand cmdBilling = new SqlCommand(strBilling, conn);
                    cmdBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)billing.InvoiceNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)Convert.ToInt32(debtorSectorId) ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)billing.UpdatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)billing.UpdatedBy ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@Balance", SqlDbType.Decimal).Value = (object)billing.Balance ?? DBNull.Value;
                    cmdBilling.Transaction = txn;
                    cmdBilling.ExecuteNonQuery();// update billing
                    cmdBilling.Dispose();

                    string strUser = "UPDATE master.recUser SET DebtorID = @DebtorID, UpdatedBy = @UpdatedBy, UpdatedDate = @UpdatedDate, UserTypeID = @UserTypeID WHERE UserID = @UserID";
                    SqlCommand cmdUser = new SqlCommand(strUser, conn);
                    cmdUser.Parameters.Add("@UserID", SqlDbType.Int).Value = user.UserID;
                    cmdUser.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtorSector.DebtorID;
                    cmdUser.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)user.UpdatedBy ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)user.UpdatedDate ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)user.UserTypeID ?? DBNull.Value;
                    cmdUser.Transaction = txn;
                    cmdUser.ExecuteNonQuery();// update user
                    cmdUser.Dispose();

                    string strUserRole = "UPDATE master.recUserRole SET RoleID = @RoleID WHERE UserID = @UserID";
                    SqlCommand cmdUserRole = new SqlCommand(strUserRole, conn);
                    cmdUserRole.Parameters.Add("@UserID", SqlDbType.Int).Value = (object)userrole.UserID ?? DBNull.Value;
                    cmdUserRole.Parameters.Add("@RoleID", SqlDbType.Int).Value = (object)userrole.RoleID ?? DBNull.Value;
                    cmdUserRole.Transaction = txn;
                    cmdUserRole.ExecuteNonQuery();// update user role menjadi debitur
                    cmdUserRole.Dispose();


                    string strPayment = "UPDATE transactions.recPayment SET DebtorSectorID = @DebtorSectorID WHERE InvoiceNo = @InvoiceNo";
                    SqlCommand cmdPayment = new SqlCommand(strPayment, conn);
                    cmdPayment.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)billing.InvoiceNo ?? DBNull.Value;
                    cmdPayment.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)Convert.ToInt32(debtorSectorId) ?? DBNull.Value;

                    cmdPayment.Transaction = txn;
                    cmdPayment.ExecuteNonQuery();// update Payment
                    cmdPayment.Dispose();

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }

        public bool Update(recSectorRegistration sectorRegistration, recInvoice invoice, recUserSector userSector, recBilling billing)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strUpdateSectorRegistration = "UPDATE transactions.recSectorRegistration SET  IsApproved = @IsApproved, ApprovedDate = @ApprovedDate, ApprovedBy = @ApprovedBy, PaymentNo = @PaymentNo, PaymentDate = @PaymentDate, PaymentValue = @PaymentValue, RegistrationStatusID = @RegistrationStatusID, DebtorSectorID = @DebtorSectorID, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE SectorRegistrationID = @SectorRegistrationID";
                    SqlCommand cmd = new SqlCommand(strUpdateSectorRegistration, conn);
                    cmd.Parameters.Add("@SectorRegistrationID", SqlDbType.Int).Value = sectorRegistration.SectorRegistrationID;
                    cmd.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = (object)sectorRegistration.IsApproved ?? DBNull.Value;
                    cmd.Parameters.Add("@ApprovedDate", SqlDbType.DateTime).Value = (object)sectorRegistration.ApprovedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@ApprovedBy", SqlDbType.VarChar).Value = (object)sectorRegistration.ApprovedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)sectorRegistration.PaymentNo ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)sectorRegistration.PaymentDate ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)sectorRegistration.PaymentValue ?? DBNull.Value;
                    cmd.Parameters.Add("@RegistrationStatusID", SqlDbType.Int).Value = sectorRegistration.RegistrationStatusID;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)sectorRegistration.DebtorSectorID ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)sectorRegistration.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)sectorRegistration.UpdatedBy ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();// update sector registration 
                    cmd.Dispose();

                    string strInvoice = "UPDATE transactions.recInvoice SET PaymentNo = @PaymentNo, PaymentDate = @PaymentDate, PaymentValue = @PaymentValue, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy, DebtorSectorID = @DebtorSectorID, Balance = @Balance WHERE InvoiceNo = @InvoiceNo";
                    SqlCommand cmdInvoice = new SqlCommand(strInvoice, conn);
                    cmdInvoice.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)invoice.InvoiceNo ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)invoice.PaymentNo ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)invoice.PaymentDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)invoice.PaymentValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)invoice.UpdatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoice.UpdatedBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)invoice.DebtorSectorID ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@Balance", SqlDbType.Decimal).Value = (object)invoice.Balance ?? DBNull.Value;
                    cmdInvoice.Transaction = txn;
                    cmdInvoice.ExecuteNonQuery();// update invoice
                    cmdInvoice.Dispose();

                    string strUserSector = "UPDATE master.recUserSector SET UserTypeID = @UserTypeID, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy, DebtorSectorID = @DebtorSectorID WHERE RegisterSectorID = @RegisterSectorID";
                    SqlCommand cmdUserSector = new SqlCommand(strUserSector, conn);
                    cmdUserSector.Parameters.Add("@RegisterSectorID", SqlDbType.Int).Value = (object)userSector.RegisterSectorID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)userSector.UserTypeID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)userSector.UpdatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)userSector.UpdatedBy ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)userSector.DebtorSectorID ?? DBNull.Value;
                    cmdUserSector.Transaction = txn;
                    cmdUserSector.ExecuteNonQuery();// update user sector
                    cmdUserSector.Dispose();

                    string strBilling = "UPDATE transactions.recBilling SET DebtorSectorID = @DebtorSectorID, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy, Balance = @Balance WHERE InvoiceNo = @InvoiceNo";
                    SqlCommand cmdBilling = new SqlCommand(strBilling, conn);
                    cmdBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)billing.InvoiceNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)billing.DebtorSectorID ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)billing.UpdatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)billing.UpdatedBy ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@Balance", SqlDbType.Decimal).Value = (object)billing.Balance ?? DBNull.Value;
                    cmdBilling.Transaction = txn;
                    cmdBilling.ExecuteNonQuery();// update billing
                    cmdBilling.Dispose();


                    string strPayment = "UPDATE transactions.recPayment SET DebtorSectorID = @DebtorSectorID WHERE InvoiceNo = @InvoiceNo";
                    SqlCommand cmdPayment = new SqlCommand(strPayment, conn);
                    cmdPayment.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)billing.InvoiceNo ?? DBNull.Value;
                    cmdPayment.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)billing.DebtorSectorID ?? DBNull.Value;

                    cmdPayment.Transaction = txn;
                    cmdPayment.ExecuteNonQuery();// update Payment
                    cmdPayment.Dispose();



                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }

        public recRegistration GetByNPWP(string key)
        {
            recRegistration t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                StringBuilder sbQuery = new StringBuilder();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    sbQuery.AppendFormat("select trr.RegistrationID,trr.NPWP,mrs.SectorName,mrss.SubSectorName,mrsss.SubSubSectorName,PreDebtorName, Address1, Address2, Address3, trr.RegionID, trr.CityID, ZipCode, Email, Phone, Fax, Website, trr.IsIndividual, EmisionTypeID, EmisionValue, {0}", Environment.NewLine);
                    sbQuery.AppendFormat("mrr.RegionCode,mrr.RegionName,mrc.CityCode,mrc.CityName from transactions.recRegistration trr  {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join transactions.recSectorRegistration trsr on trsr.RegistrationID=trr.RegistrationID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recSector mrs on mrs.SectorID=trsr.SectorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recSubSector mrss on mrss.SubSectorID=trsr.SubSectorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recSubSubSector mrsss on mrsss.SubSubSectorID=trsr.SubSubSectorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("left join master.recRegion mrr on  mrr.RegionID= trr.RegionID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("left join master.recCity mrc on mrc.CityID=trr.CityID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("where trr.NPWP = @NPWP {0}", Environment.NewLine);
                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = key;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recRegistration(true);
                                t.RegistrationID = dr[recRegistration.Fields.RegistrationID] == DBNull.Value ? 0 : int.Parse(dr[recRegistration.Fields.RegistrationID].ToString());
                                t.NPWP = dr[recRegistration.Fields.NPWP] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.NPWP].ToString();
                                t.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();
                                t.recSubSector.SubSectorName = dr[recSubSector.Fields.SubSectorName] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorName].ToString();
                                t.recSector.SectorName = dr[recSector.Fields.SectorName] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorName].ToString();
                                t.recSectorRegistration.EmisionTypeID = dr[recSectorRegistration.Fields.EmisionTypeID] == DBNull.Value ? (short?)null : short.Parse(dr[recSectorRegistration.Fields.EmisionTypeID].ToString());
                                t.recSectorRegistration.EmisionValue = dr[recSectorRegistration.Fields.EmisionValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recSectorRegistration.Fields.EmisionValue].ToString());
                                t.PreDebtorName = dr[recRegistration.Fields.PreDebtorName] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.PreDebtorName].ToString();
                                t.Address1 = dr[recRegistration.Fields.Address1] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Address1].ToString();
                                t.Address2 = dr[recRegistration.Fields.Address2] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Address2].ToString();
                                t.Address3 = dr[recRegistration.Fields.Address3] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Address3].ToString();
                                t.RegionID = dr[recRegistration.Fields.RegionID] == DBNull.Value ? (int?)null : int.Parse(dr[recRegistration.Fields.RegionID].ToString());
                                t.CityID = dr[recRegistration.Fields.CityID] == DBNull.Value ? (int?)null : int.Parse(dr[recRegistration.Fields.CityID].ToString());
                                t.ZipCode = dr[recRegistration.Fields.ZipCode] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.ZipCode].ToString();
                                t.Email = dr[recRegistration.Fields.Email] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Email].ToString();
                                t.Phone = dr[recRegistration.Fields.Phone] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Phone].ToString();
                                t.Fax = dr[recRegistration.Fields.Fax] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Fax].ToString();
                                t.Website = dr[recRegistration.Fields.Website] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Website].ToString();
                                t.IsIndividual = dr[recRegistration.Fields.IsIndividual] == DBNull.Value ? (bool?)null : bool.Parse(dr[recRegistration.Fields.IsIndividual].ToString());
                                t.recRegion.RegionName = dr[recRegion.Fields.RegionName] == DBNull.Value ? string.Empty : dr[recRegion.Fields.RegionName].ToString();
                                t.recCity.CityName = dr[recCity.Fields.CityName] == DBNull.Value ? string.Empty : dr[recCity.Fields.CityName].ToString();
                                t.recRegion.RegionCode = dr[recRegion.Fields.RegionCode] == DBNull.Value ? string.Empty : dr[recRegion.Fields.RegionCode].ToString();
                                t.recCity.CityCode = dr[recCity.Fields.CityCode] == DBNull.Value ? string.Empty : dr[recCity.Fields.CityCode].ToString();

                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }
        public List<recRegistration> GetByNPWPs(string key)
        {
            List<recRegistration> tt = new List<recRegistration>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                StringBuilder sbQuery = new StringBuilder();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    sbQuery.AppendFormat("select trr.RegistrationID,trr.NPWP,mrs.SectorName,mrss.SubSectorName,mrsss.SubSubSectorName,PreDebtorName, Address1, Address2, Address3, trr.RegionID, trr.CityID, ZipCode, Email, Phone, Fax, Website, trr.IsIndividual, EmisionTypeID, EmisionValue, {0}", Environment.NewLine);
                    sbQuery.AppendFormat("mrr.RegionCode,mrr.RegionName,mrc.CityCode,mrc.CityName from transactions.recRegistration trr  {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join transactions.recSectorRegistration trsr on trsr.RegistrationID=trr.RegistrationID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recSector mrs on mrs.SectorID=trsr.SectorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recSubSector mrss on mrss.SubSectorID=trsr.SubSectorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recSubSubSector mrsss on mrsss.SubSubSectorID=trsr.SubSubSectorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("left join master.recRegion mrr on  mrr.RegionID= trr.RegionID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("left join master.recCity mrc on mrc.CityID=trr.CityID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("where trr.NPWP = @NPWP {0}", Environment.NewLine);
                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = key;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recRegistration t = new recRegistration(true);
                                t.RegistrationID = dr[recRegistration.Fields.RegistrationID] == DBNull.Value ? 0 : int.Parse(dr[recRegistration.Fields.RegistrationID].ToString());
                                t.NPWP = dr[recRegistration.Fields.NPWP] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.NPWP].ToString();
                                t.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();
                                t.recSubSector.SubSectorName = dr[recSubSector.Fields.SubSectorName] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorName].ToString();
                                t.recSector.SectorName = dr[recSector.Fields.SectorName] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorName].ToString();
                                t.recSectorRegistration.EmisionTypeID = dr[recSectorRegistration.Fields.EmisionTypeID] == DBNull.Value ? (short?)null : short.Parse(dr[recSectorRegistration.Fields.EmisionTypeID].ToString());
                                t.recSectorRegistration.EmisionValue = dr[recSectorRegistration.Fields.EmisionValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recSectorRegistration.Fields.EmisionValue].ToString());
                                t.PreDebtorName = dr[recRegistration.Fields.PreDebtorName] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.PreDebtorName].ToString();
                                t.Address1 = dr[recRegistration.Fields.Address1] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Address1].ToString();
                                t.Address2 = dr[recRegistration.Fields.Address2] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Address2].ToString();
                                t.Address3 = dr[recRegistration.Fields.Address3] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Address3].ToString();
                                t.RegionID = dr[recRegistration.Fields.RegionID] == DBNull.Value ? (int?)null : int.Parse(dr[recRegistration.Fields.RegionID].ToString());
                                t.CityID = dr[recRegistration.Fields.CityID] == DBNull.Value ? (int?)null : int.Parse(dr[recRegistration.Fields.CityID].ToString());
                                t.ZipCode = dr[recRegistration.Fields.ZipCode] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.ZipCode].ToString();
                                t.Email = dr[recRegistration.Fields.Email] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Email].ToString();
                                t.Phone = dr[recRegistration.Fields.Phone] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Phone].ToString();
                                t.Fax = dr[recRegistration.Fields.Fax] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Fax].ToString();
                                t.Website = dr[recRegistration.Fields.Website] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Website].ToString();
                                t.IsIndividual = dr[recRegistration.Fields.IsIndividual] == DBNull.Value ? (bool?)null : bool.Parse(dr[recRegistration.Fields.IsIndividual].ToString());
                                t.recRegion.RegionName = dr[recRegion.Fields.RegionName] == DBNull.Value ? string.Empty : dr[recRegion.Fields.RegionName].ToString();
                                t.recCity.CityName = dr[recCity.Fields.CityName] == DBNull.Value ? string.Empty : dr[recCity.Fields.CityName].ToString();
                                t.recRegion.RegionCode = dr[recRegion.Fields.RegionCode] == DBNull.Value ? string.Empty : dr[recRegion.Fields.RegionCode].ToString();
                                t.recCity.CityCode = dr[recCity.Fields.CityCode] == DBNull.Value ? string.Empty : dr[recCity.Fields.CityCode].ToString();
                                tt.Add(t);
                                t.Dispose();

                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
        public recRegistration GetByIdCopyToDebtor(int key)
        {
            recRegistration t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                StringBuilder sbQuery = new StringBuilder();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    sbQuery.AppendFormat("select trr.PreDebtorName,trr.NPWP,trr.Address1,trr.Address2,trr.Address3, {0}", Environment.NewLine);
                    sbQuery.AppendFormat("trr.ZipCode,trr.RegionID,trr.CityID,trr.Email,trr.Phone,trr.Website, {0}", Environment.NewLine);
                    sbQuery.AppendFormat("trr.IsActive,trr.Fax,trr.IsIndividual from transactions.recRegistration trr {0}", Environment.NewLine);
                    sbQuery.AppendFormat("where trr.RegistrationID = @RegistrationID {0}", Environment.NewLine);
                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@RegistrationID", SqlDbType.Int).Value = key;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recRegistration(true);
                                t.PreDebtorName = dr[recRegistration.Fields.PreDebtorName] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.PreDebtorName].ToString();
                                t.NPWP = dr[recRegistration.Fields.NPWP] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.NPWP].ToString();
                                t.Address1 = dr[recRegistration.Fields.Address1] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Address1].ToString();
                                t.Address2 = dr[recRegistration.Fields.Address2] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Address2].ToString();
                                t.Address3 = dr[recRegistration.Fields.Address3] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Address3].ToString();
                                t.RegionID = dr[recRegistration.Fields.RegionID] == DBNull.Value ? (int?)null : int.Parse(dr[recRegistration.Fields.RegionID].ToString());
                                t.CityID = dr[recRegistration.Fields.CityID] == DBNull.Value ? (int?)null : int.Parse(dr[recRegistration.Fields.CityID].ToString());
                                t.ZipCode = dr[recRegistration.Fields.ZipCode] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.ZipCode].ToString();
                                t.Email = dr[recRegistration.Fields.Email] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Email].ToString();
                                t.Phone = dr[recRegistration.Fields.Phone] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Phone].ToString();
                                t.Fax = dr[recRegistration.Fields.Fax] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Fax].ToString();
                                t.Website = dr[recRegistration.Fields.Website] == DBNull.Value ? string.Empty : dr[recRegistration.Fields.Website].ToString();
                                t.IsActive = dr[recRegistration.Fields.IsActive] == DBNull.Value ? (bool?)null : bool.Parse(dr[recRegistration.Fields.IsActive].ToString());
                                t.IsIndividual = dr[recRegistration.Fields.IsIndividual] == DBNull.Value ? (bool?)null : bool.Parse(dr[recRegistration.Fields.IsIndividual].ToString());
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }
        public bool Update(recDebtor debtor, recDebtorSector debtorSector, recUser user, recUserSector userSector)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strCommandMaster = "UPDATE master.recDebtor SET DebtorCode = @DebtorCode, DebtorName = @DebtorName, NPWP = @NPWP, Address1 = @Address1, Address2 = @Address2, Address3 = @Address3, ZipCode = @ZipCode, RegionID = @RegionID, CityID = @CityID, Email = @Email, Phone = @Phone, Fax = @Fax, Website = @Website, IsActive = @IsActive, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE DebtorID = @DebtorID";
                    SqlCommand cmd = new SqlCommand(strCommandMaster, conn);
                    cmd.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtor.DebtorID;
                    cmd.Parameters.Add("@DebtorCode", SqlDbType.VarChar).Value = debtor.DebtorCode;
                    cmd.Parameters.Add("@DebtorName", SqlDbType.VarChar).Value = debtor.DebtorName;
                    cmd.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = debtor.NPWP;
                    cmd.Parameters.Add("@Address1", SqlDbType.VarChar).Value = (object)debtor.Address1 ?? DBNull.Value;
                    cmd.Parameters.Add("@Address2", SqlDbType.VarChar).Value = (object)debtor.Address2 ?? DBNull.Value;
                    cmd.Parameters.Add("@Address3", SqlDbType.VarChar).Value = (object)debtor.Address3 ?? DBNull.Value;
                    cmd.Parameters.Add("@ZipCode", SqlDbType.VarChar).Value = (object)debtor.ZipCode ?? DBNull.Value;
                    cmd.Parameters.Add("@RegionID", SqlDbType.Int).Value = (object)debtor.RegionID ?? DBNull.Value;
                    cmd.Parameters.Add("@CityID", SqlDbType.Int).Value = (object)debtor.CityID ?? DBNull.Value;
                    cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)debtor.Email ?? DBNull.Value;
                    cmd.Parameters.Add("@Phone", SqlDbType.VarChar).Value = (object)debtor.Phone ?? DBNull.Value;
                    cmd.Parameters.Add("@Fax", SqlDbType.VarChar).Value = (object)debtor.Fax ?? DBNull.Value;
                    cmd.Parameters.Add("@Website", SqlDbType.VarChar).Value = (object)debtor.Website ?? DBNull.Value;
                    cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = debtor.IsActive;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)debtor.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)debtor.UpdatedBy ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // Update Debtor
                    cmd.Dispose();

                    string strDebtorSector = "INSERT INTO master.recDebtorSector(DebtorID, SectorID, SubSectorID, SubSubSectorID, IsActive, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@DebtorID, @SectorID, @SubSectorID, @SubSubSectorID, @IsActive, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdDebtorSector = new SqlCommand(strDebtorSector, conn);
                    cmdDebtorSector.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtorSector.DebtorID;
                    cmdDebtorSector.Parameters.Add("@SectorID", SqlDbType.Int).Value = debtorSector.SectorID;
                    cmdDebtorSector.Parameters.Add("@SubSectorID", SqlDbType.Int).Value = debtorSector.SubSectorID;
                    cmdDebtorSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = debtorSector.SubSubSectorID;
                    cmdDebtorSector.Parameters.Add("@IsActive", SqlDbType.Bit).Value = debtorSector.IsActive;
                    cmdDebtorSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)debtorSector.CreatedDate ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)debtorSector.CreatedBy ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)debtorSector.UpdatedDate ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)debtorSector.UpdatedBy ?? DBNull.Value;
                    cmdDebtorSector.Transaction = txn;
                    cmdDebtorSector.ExecuteNonQuery(); // Save DebtorSector
                    cmdDebtorSector.Dispose();

                    string strCommandUser = "INSERT INTO master.recUser(UserCode, UserName, Email, Password, IsActive, Mobile, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate) VALUES(@UserCode, @UserName, @Email, @Password, @IsActive, @Mobile, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate)";
                    SqlCommand cmdUser = new SqlCommand(strCommandUser, conn);
                    cmdUser.Parameters.Add("@UserCode", SqlDbType.VarChar).Value = user.UserCode;
                    cmdUser.Parameters.Add("@UserName", SqlDbType.VarChar).Value = (object)user.UserName ?? DBNull.Value;
                    cmdUser.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)user.Email ?? DBNull.Value;
                    cmdUser.Parameters.Add("@Password", SqlDbType.VarChar).Value = user.Password;
                    cmdUser.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)user.IsActive ?? DBNull.Value;
                    cmdUser.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = (object)user.Mobile ?? DBNull.Value;
                    cmdUser.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)user.CreatedBy ?? DBNull.Value;
                    cmdUser.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)user.CreatedDate ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)user.UpdatedBy ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)user.UpdatedDate ?? DBNull.Value;
                    cmdUser.Transaction = txn;
                    cmdUser.ExecuteNonQuery();//save recUser
                    cmdUser.Dispose();

                    string strCommandGetUserId = " SELECT MAX(UserID) UserID FROM master.recUser WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetUserId = new SqlCommand(strCommandGetUserId, conn);
                    cmdGetUserId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = user.CreatedBy;
                    cmdGetUserId.Transaction = txn;
                    var objUserId = cmdGetUserId.ExecuteScalar(); // Get User Id
                    cmdGetUserId.Dispose();

                    decimal UserId = objUserId == DBNull.Value ? 0m : Convert.ToDecimal(objUserId.ToString()); // Get User Id

                    string strGetDebtorSectorId = " SELECT MAX(DebtorSectorID) DebtorSectorID FROM master.recDebtorSector WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetDebtorSectorId = new SqlCommand(strGetDebtorSectorId, conn);
                    cmdGetDebtorSectorId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = debtorSector.CreatedBy;
                    cmdGetDebtorSectorId.Transaction = txn;
                    var objDebtorSectorId = cmdGetDebtorSectorId.ExecuteScalar(); // Get DebtorSectorId
                    cmdGetDebtorSectorId.Dispose();

                    decimal DebtorSectorId = objDebtorSectorId == DBNull.Value ? 0m : Convert.ToDecimal(objDebtorSectorId.ToString()); // Get DebtorSectorId

                    string strCommandUserSector = "INSERT INTO master.recUserSector(RegisterSectorID, DebtorSectorID, SubSubSectorID, UserTypeID, UserID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@RegisterSectorID, @DebtorSectorID, @SubSubSectorID, @UserTypeID, @UserID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdUserSector = new SqlCommand(strCommandUserSector, conn);
                    cmdUserSector.Parameters.Add("@RegisterSectorID", SqlDbType.Int).Value = (object)userSector.RegisterSectorID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = Convert.ToInt32(DebtorSectorId);
                    cmdUserSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = userSector.SubSubSectorID;
                    cmdUserSector.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)userSector.UserTypeID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UserID", SqlDbType.Int).Value = Convert.ToInt32(UserId);
                    cmdUserSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)userSector.CreatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)userSector.CreatedBy ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)userSector.UpdatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)userSector.UpdatedBy ?? DBNull.Value;
                    cmdUserSector.Transaction = txn;
                    cmdUserSector.ExecuteNonQuery();// save recUserSector
                    cmdUserSector.Dispose();

                    result = true;
                    txn.Commit();
                }

                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }
        public bool Save(recDebtor debtor, recDebtorSector debtorSector, recUser user, recUserSector userSector, recUserRole userRole)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strDebtor = "INSERT INTO master.recDebtor(DebtorCode, DebtorName, NPWP, Address1, Address2, Address3, ZipCode, RegionID, CityID, Email, Phone, Fax, Website, IsActive, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, IsIndividual) VALUES(@DebtorCode, @DebtorName, @NPWP, @Address1, @Address2, @Address3, @ZipCode, @RegionID, @CityID, @Email, @Phone, @Fax, @Website, @IsActive, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy, @IsIndividual)";
                    SqlCommand cmd = new SqlCommand(strDebtor, conn);
                    cmd.Parameters.Add("@DebtorCode", SqlDbType.VarChar).Value = debtor.DebtorCode;
                    cmd.Parameters.Add("@DebtorName", SqlDbType.VarChar).Value = debtor.DebtorName;
                    cmd.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = debtor.NPWP;
                    cmd.Parameters.Add("@Address1", SqlDbType.VarChar).Value = (object)debtor.Address1 ?? DBNull.Value;
                    cmd.Parameters.Add("@Address2", SqlDbType.VarChar).Value = (object)debtor.Address2 ?? DBNull.Value;
                    cmd.Parameters.Add("@Address3", SqlDbType.VarChar).Value = (object)debtor.Address3 ?? DBNull.Value;
                    cmd.Parameters.Add("@ZipCode", SqlDbType.VarChar).Value = (object)debtor.ZipCode ?? DBNull.Value;
                    cmd.Parameters.Add("@RegionID", SqlDbType.Int).Value = (object)debtor.RegionID ?? DBNull.Value;
                    cmd.Parameters.Add("@CityID", SqlDbType.Int).Value = (object)debtor.CityID ?? DBNull.Value;
                    cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)debtor.Email ?? DBNull.Value;
                    cmd.Parameters.Add("@Phone", SqlDbType.VarChar).Value = (object)debtor.Phone ?? DBNull.Value;
                    cmd.Parameters.Add("@Fax", SqlDbType.VarChar).Value = (object)debtor.Fax ?? DBNull.Value;
                    cmd.Parameters.Add("@Website", SqlDbType.VarChar).Value = (object)debtor.Website ?? DBNull.Value;
                    cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = debtor.IsActive;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)debtor.CreatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)debtor.CreatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)debtor.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)debtor.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@IsIndividual", SqlDbType.Bit).Value = (object)debtor.IsIndividual ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // save Debtor
                    cmd.Dispose();

                    string strGetDebtorId = " SELECT MAX(DebtorID) DebtorID FROM master.recDebtor WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetDebtorId = new SqlCommand(strGetDebtorId, conn);
                    cmdGetDebtorId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = debtor.CreatedBy;
                    cmdGetDebtorId.Transaction = txn;
                    var objDebtorId = cmdGetDebtorId.ExecuteScalar(); // Get Debtor Id
                    cmdGetDebtorId.Dispose();

                    decimal DebtorId = objDebtorId == DBNull.Value ? 0m : Convert.ToDecimal(objDebtorId.ToString()); // Get Debtor Id

                    string strDebtorSector = "INSERT INTO master.recDebtorSector(DebtorID, SectorID, SubSectorID, SubSubSectorID, IsActive, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@DebtorID, @SectorID, @SubSectorID, @SubSubSectorID, @IsActive, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdDebtorSector = new SqlCommand(strDebtorSector, conn);
                    cmdDebtorSector.Parameters.Add("@DebtorID", SqlDbType.Int).Value = DebtorId;
                    cmdDebtorSector.Parameters.Add("@SectorID", SqlDbType.Int).Value = debtorSector.SectorID;
                    cmdDebtorSector.Parameters.Add("@SubSectorID", SqlDbType.Int).Value = debtorSector.SubSectorID;
                    cmdDebtorSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = debtorSector.SubSubSectorID;
                    cmdDebtorSector.Parameters.Add("@IsActive", SqlDbType.Bit).Value = debtorSector.IsActive;
                    cmdDebtorSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)debtorSector.CreatedDate ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)debtorSector.CreatedBy ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)debtorSector.UpdatedDate ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)debtorSector.UpdatedBy ?? DBNull.Value;
                    cmdDebtorSector.Transaction = txn;
                    cmdDebtorSector.ExecuteNonQuery(); // Save DebtorSector
                    cmdDebtorSector.Dispose();

                    string strCommandUser = "INSERT INTO master.recUser(UserCode, UserName, Email, Password, IsActive, Mobile, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate, DebtorID, UserTypeID) VALUES(@UserCode, @UserName, @Email, @Password, @IsActive, @Mobile, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate, @DebtorID, @UserTypeID)";
                    SqlCommand cmdUser = new SqlCommand(strCommandUser, conn);
                    cmdUser.Parameters.Add("@UserCode", SqlDbType.VarChar).Value = user.UserCode;
                    cmdUser.Parameters.Add("@UserName", SqlDbType.VarChar).Value = (object)user.UserName ?? DBNull.Value;
                    cmdUser.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)user.Email ?? DBNull.Value;
                    cmdUser.Parameters.Add("@Password", SqlDbType.VarChar).Value = user.Password;
                    cmdUser.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)user.IsActive ?? DBNull.Value;
                    cmdUser.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = (object)user.Mobile ?? DBNull.Value;
                    cmdUser.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)user.CreatedBy ?? DBNull.Value;
                    cmdUser.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)user.CreatedDate ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)user.UpdatedBy ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)user.UpdatedDate ?? DBNull.Value;
                    cmdUser.Parameters.Add("@DebtorID", SqlDbType.Int).Value = (object)Convert.ToInt32(DebtorId) ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)user.UserTypeID ?? DBNull.Value;
                    cmdUser.Transaction = txn;
                    cmdUser.ExecuteNonQuery();//save recUser
                    cmdUser.Dispose();

                    string strCommandGetUserId = " SELECT MAX(UserID) UserID FROM master.recUser WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetUserId = new SqlCommand(strCommandGetUserId, conn);
                    cmdGetUserId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = user.CreatedBy;
                    cmdGetUserId.Transaction = txn;
                    var objUserId = cmdGetUserId.ExecuteScalar(); // Get User Id
                    cmdGetUserId.Dispose();

                    decimal UserId = objUserId == DBNull.Value ? 0m : Convert.ToDecimal(objUserId.ToString()); // Get User Id

                    string strGetDebtorSectorId = " SELECT MAX(DebtorSectorID) DebtorSectorID FROM master.recDebtorSector WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetDebtorSectorId = new SqlCommand(strGetDebtorSectorId, conn);
                    cmdGetDebtorSectorId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = debtorSector.CreatedBy;
                    cmdGetDebtorSectorId.Transaction = txn;
                    var objDebtorSectorId = cmdGetDebtorSectorId.ExecuteScalar(); // Get DebtorSectorId
                    cmdGetDebtorSectorId.Dispose();

                    decimal DebtorSectorId = objDebtorSectorId == DBNull.Value ? 0m : Convert.ToDecimal(objDebtorSectorId.ToString()); // Get DebtorSectorId

                    string strCommandUserSector = "INSERT INTO master.recUserSector(RegisterSectorID, DebtorSectorID, SubSubSectorID, UserTypeID, UserID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@RegisterSectorID, @DebtorSectorID, @SubSubSectorID, @UserTypeID, @UserID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdUserSector = new SqlCommand(strCommandUserSector, conn);
                    cmdUserSector.Parameters.Add("@RegisterSectorID", SqlDbType.Int).Value = (object)userSector.RegisterSectorID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = Convert.ToInt32(DebtorSectorId);
                    cmdUserSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = userSector.SubSubSectorID;
                    cmdUserSector.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)userSector.UserTypeID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UserID", SqlDbType.Int).Value = Convert.ToInt32(UserId);
                    cmdUserSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)userSector.CreatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)userSector.CreatedBy ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)userSector.UpdatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)userSector.UpdatedBy ?? DBNull.Value;
                    cmdUserSector.Transaction = txn;
                    cmdUserSector.ExecuteNonQuery();// save recUserSector
                    cmdUserSector.Dispose();

                    string struserrole = "INSERT INTO master.recUserRole(UserID, RoleID, CreatedDate, CreatedBy) VALUES(@UserID, @RoleID, @CreatedDate, @CreatedBy)";
                    SqlCommand cmdUserRole = new SqlCommand(struserrole, conn);
                    cmdUserRole.Parameters.Add("@UserID", SqlDbType.Int).Value = Convert.ToInt32(UserId);
                    cmdUserRole.Parameters.Add("@RoleID", SqlDbType.Int).Value = (object)userRole.RoleID ?? DBNull.Value;
                    cmdUserRole.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)userRole.CreatedDate ?? DBNull.Value;
                    cmdUserRole.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)userRole.CreatedBy ?? DBNull.Value;
                    cmdUserRole.Transaction = txn;
                    cmdUserRole.ExecuteNonQuery();//save user role
                    cmdUserRole.Dispose();

                    result = true;
                    txn.Commit();
                }

                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }
        public bool Save(recDebtor debtor, recDebtorSector debtorSector, recUserSector userSector)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strDebtor = "INSERT INTO master.recDebtor(DebtorCode, DebtorName, NPWP, Address1, Address2, Address3, ZipCode, RegionID, CityID, Email, Phone, Fax, Website, IsActive, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@DebtorCode, @DebtorName, @NPWP, @Address1, @Address2, @Address3, @ZipCode, @RegionID, @CityID, @Email, @Phone, @Fax, @Website, @IsActive, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmd = new SqlCommand(strDebtor, conn);
                    cmd.Parameters.Add("@DebtorCode", SqlDbType.VarChar).Value = debtor.DebtorCode;
                    cmd.Parameters.Add("@DebtorName", SqlDbType.VarChar).Value = debtor.DebtorName;
                    cmd.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = debtor.NPWP;
                    cmd.Parameters.Add("@Address1", SqlDbType.VarChar).Value = (object)debtor.Address1 ?? DBNull.Value;
                    cmd.Parameters.Add("@Address2", SqlDbType.VarChar).Value = (object)debtor.Address2 ?? DBNull.Value;
                    cmd.Parameters.Add("@Address3", SqlDbType.VarChar).Value = (object)debtor.Address3 ?? DBNull.Value;
                    cmd.Parameters.Add("@ZipCode", SqlDbType.VarChar).Value = (object)debtor.ZipCode ?? DBNull.Value;
                    cmd.Parameters.Add("@RegionID", SqlDbType.Int).Value = (object)debtor.RegionID ?? DBNull.Value;
                    cmd.Parameters.Add("@CityID", SqlDbType.Int).Value = (object)debtor.CityID ?? DBNull.Value;
                    cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)debtor.Email ?? DBNull.Value;
                    cmd.Parameters.Add("@Phone", SqlDbType.VarChar).Value = (object)debtor.Phone ?? DBNull.Value;
                    cmd.Parameters.Add("@Fax", SqlDbType.VarChar).Value = (object)debtor.Fax ?? DBNull.Value;
                    cmd.Parameters.Add("@Website", SqlDbType.VarChar).Value = (object)debtor.Website ?? DBNull.Value;
                    cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = debtor.IsActive;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)debtor.CreatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)debtor.CreatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)debtor.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)debtor.UpdatedBy ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // save Debtor
                    cmd.Dispose();

                    string strGetDebtorId = " SELECT MAX(DebtorID) DebtorID FROM master.recDebtor WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetDebtorId = new SqlCommand(strGetDebtorId, conn);
                    cmdGetDebtorId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = debtor.CreatedBy;
                    cmdGetDebtorId.Transaction = txn;
                    var objDebtorId = cmdGetDebtorId.ExecuteScalar(); // Get Debtor Id
                    cmdGetDebtorId.Dispose();

                    decimal DebtorId = objDebtorId == DBNull.Value ? 0m : Convert.ToDecimal(objDebtorId.ToString()); // Get Debtor Id

                    string strDebtorSector = "INSERT INTO master.recDebtorSector(DebtorID, SectorID, SubSectorID, SubSubSectorID, IsActive, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@DebtorID, @SectorID, @SubSectorID, @SubSubSectorID, @IsActive, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdDebtorSector = new SqlCommand(strDebtorSector, conn);
                    cmdDebtorSector.Parameters.Add("@DebtorID", SqlDbType.Int).Value = DebtorId;
                    cmdDebtorSector.Parameters.Add("@SectorID", SqlDbType.Int).Value = debtorSector.SectorID;
                    cmdDebtorSector.Parameters.Add("@SubSectorID", SqlDbType.Int).Value = debtorSector.SubSectorID;
                    cmdDebtorSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = debtorSector.SubSubSectorID;
                    cmdDebtorSector.Parameters.Add("@IsActive", SqlDbType.Bit).Value = debtorSector.IsActive;
                    cmdDebtorSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)debtorSector.CreatedDate ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)debtorSector.CreatedBy ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)debtorSector.UpdatedDate ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)debtorSector.UpdatedBy ?? DBNull.Value;
                    cmdDebtorSector.Transaction = txn;
                    cmdDebtorSector.ExecuteNonQuery(); // Save DebtorSector
                    cmdDebtorSector.Dispose();

                    string strGetDebtorSectorId = " SELECT MAX(DebtorSectorID) DebtorSectorID FROM master.recDebtorSector WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetDebtorSectorId = new SqlCommand(strGetDebtorSectorId, conn);
                    cmdGetDebtorSectorId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = debtorSector.CreatedBy;
                    cmdGetDebtorSectorId.Transaction = txn;
                    var objDebtorSectorId = cmdGetDebtorSectorId.ExecuteScalar(); // Get DebtorSectorId
                    cmdGetDebtorSectorId.Dispose();

                    decimal DebtorSectorId = objDebtorSectorId == DBNull.Value ? 0m : Convert.ToDecimal(objDebtorSectorId.ToString()); // Get DebtorSectorId

                    string strCommandUserSector = "INSERT INTO master.recUserSector(RegisterSectorID, DebtorSectorID, SubSubSectorID, UserTypeID, UserID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@RegisterSectorID, @DebtorSectorID, @SubSubSectorID, @UserTypeID, @UserID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdUserSector = new SqlCommand(strCommandUserSector, conn);
                    cmdUserSector.Parameters.Add("@RegisterSectorID", SqlDbType.Int).Value = (object)userSector.RegisterSectorID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = Convert.ToInt32(DebtorSectorId);
                    cmdUserSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = userSector.SubSubSectorID;
                    cmdUserSector.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)userSector.UserTypeID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UserID", SqlDbType.Int).Value = (object)userSector.UserID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)userSector.CreatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)userSector.CreatedBy ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)userSector.UpdatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)userSector.UpdatedBy ?? DBNull.Value;
                    cmdUserSector.Transaction = txn;
                    cmdUserSector.ExecuteNonQuery();// save recUserSector
                    cmdUserSector.Dispose();

                    result = true;
                    txn.Commit();
                }

                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }
        public bool SaveAndUpdate(recDebtor debtor, recDebtorSector debtorSector, recUser user, recUserSector userSector, recUserRole userRole)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strUpdateDebtor = "UPDATE master.recDebtor SET  DebtorName = @DebtorName, NPWP = @NPWP, Address1 = @Address1, Address2 = @Address2, Address3 = @Address3, ZipCode = @ZipCode, RegionID = @RegionID, CityID = @CityID, Email = @Email, Phone = @Phone, Fax = @Fax, Website = @Website, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE DebtorID = @DebtorID ";
                    SqlCommand cmdDebtor = new SqlCommand(strUpdateDebtor, conn);
                    cmdDebtor.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtor.DebtorID;
                    //cmdDebtor.Parameters.Add("@DebtorCode", SqlDbType.VarChar).Value = debtor.DebtorCode;
                    cmdDebtor.Parameters.Add("@DebtorName", SqlDbType.VarChar).Value = debtor.DebtorName;
                    cmdDebtor.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = debtor.NPWP;
                    cmdDebtor.Parameters.Add("@Address1", SqlDbType.VarChar).Value = (object)debtor.Address1 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Address2", SqlDbType.VarChar).Value = (object)debtor.Address2 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Address3", SqlDbType.VarChar).Value = (object)debtor.Address3 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@ZipCode", SqlDbType.VarChar).Value = (object)debtor.ZipCode ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@RegionID", SqlDbType.Int).Value = (object)debtor.RegionID ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@CityID", SqlDbType.Int).Value = (object)debtor.CityID ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)debtor.Email ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Phone", SqlDbType.VarChar).Value = (object)debtor.Phone ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Fax", SqlDbType.VarChar).Value = (object)debtor.Fax ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Website", SqlDbType.VarChar).Value = (object)debtor.Website ?? DBNull.Value;
                    //cmdDebtor.Parameters.Add("@IsActive", SqlDbType.Bit).Value = debtor.IsActive;
                    cmdDebtor.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)debtor.UpdatedDate ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)debtor.UpdatedBy ?? DBNull.Value;
                    //cmdDebtor.Parameters.Add("@IsIndividual", SqlDbType.Bit).Value = debtor.IsIndividual;
                    cmdDebtor.Transaction = txn;
                    cmdDebtor.ExecuteNonQuery(); // update Debtor
                    cmdDebtor.Dispose();

                    string strDebtorSector = "INSERT INTO master.recDebtorSector(DebtorID, SectorID, SubSectorID, SubSubSectorID, IsActive, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@DebtorID, @SectorID, @SubSectorID, @SubSubSectorID, @IsActive, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdDebtorSector = new SqlCommand(strDebtorSector, conn);
                    cmdDebtorSector.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtorSector.DebtorID;
                    cmdDebtorSector.Parameters.Add("@SectorID", SqlDbType.Int).Value = debtorSector.SectorID;
                    cmdDebtorSector.Parameters.Add("@SubSectorID", SqlDbType.Int).Value = debtorSector.SubSectorID;
                    cmdDebtorSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = debtorSector.SubSubSectorID;
                    cmdDebtorSector.Parameters.Add("@IsActive", SqlDbType.Bit).Value = debtorSector.IsActive;
                    cmdDebtorSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)debtorSector.CreatedDate ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)debtorSector.CreatedBy ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)debtorSector.UpdatedDate ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)debtorSector.UpdatedBy ?? DBNull.Value;
                    cmdDebtorSector.Transaction = txn;
                    cmdDebtorSector.ExecuteNonQuery(); // Save DebtorSector
                    cmdDebtorSector.Dispose();

                    string strCommandUser = "INSERT INTO master.recUser(UserCode, UserName, Email, Password, IsActive, Mobile, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate, DebtorID, UserTypeID) VALUES(@UserCode, @UserName, @Email, @Password, @IsActive, @Mobile, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate, @DebtorID, @UserTypeID)";
                    SqlCommand cmdUser = new SqlCommand(strCommandUser, conn);
                    cmdUser.Parameters.Add("@UserCode", SqlDbType.VarChar).Value = user.UserCode;
                    cmdUser.Parameters.Add("@UserName", SqlDbType.VarChar).Value = (object)user.UserName ?? DBNull.Value;
                    cmdUser.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)user.Email ?? DBNull.Value;
                    cmdUser.Parameters.Add("@Password", SqlDbType.VarChar).Value = user.Password;
                    cmdUser.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)user.IsActive ?? DBNull.Value;
                    cmdUser.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = (object)user.Mobile ?? DBNull.Value;
                    cmdUser.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)user.CreatedBy ?? DBNull.Value;
                    cmdUser.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)user.CreatedDate ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)user.UpdatedBy ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)user.UpdatedDate ?? DBNull.Value;
                    cmdUser.Parameters.Add("@DebtorID", SqlDbType.Int).Value = (object)debtorSector.DebtorID ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)user.UserTypeID ?? DBNull.Value;
                    cmdUser.Transaction = txn;
                    cmdUser.ExecuteNonQuery();//save recUser
                    cmdUser.Dispose();

                    string strCommandGetUserId = " SELECT MAX(UserID) UserID FROM master.recUser WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetUserId = new SqlCommand(strCommandGetUserId, conn);
                    cmdGetUserId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = user.CreatedBy;
                    cmdGetUserId.Transaction = txn;
                    var objUserId = cmdGetUserId.ExecuteScalar(); // Get User Id
                    cmdGetUserId.Dispose();

                    decimal UserId = objUserId == DBNull.Value ? 0m : Convert.ToDecimal(objUserId.ToString()); // Get User Id

                    string strGetDebtorSectorId = " SELECT MAX(DebtorSectorID) DebtorSectorID FROM master.recDebtorSector WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetDebtorSectorId = new SqlCommand(strGetDebtorSectorId, conn);
                    cmdGetDebtorSectorId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = debtorSector.CreatedBy;
                    cmdGetDebtorSectorId.Transaction = txn;
                    var objDebtorSectorId = cmdGetDebtorSectorId.ExecuteScalar(); // Get DebtorSectorId
                    cmdGetDebtorSectorId.Dispose();

                    decimal DebtorSectorId = objDebtorSectorId == DBNull.Value ? 0m : Convert.ToDecimal(objDebtorSectorId.ToString()); // Get DebtorSectorId

                    string strCommandUserSector = "INSERT INTO master.recUserSector(RegisterSectorID, DebtorSectorID, SubSubSectorID, UserTypeID, UserID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@RegisterSectorID, @DebtorSectorID, @SubSubSectorID, @UserTypeID, @UserID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdUserSector = new SqlCommand(strCommandUserSector, conn);
                    cmdUserSector.Parameters.Add("@RegisterSectorID", SqlDbType.Int).Value = (object)userSector.RegisterSectorID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = Convert.ToInt32(DebtorSectorId);
                    cmdUserSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = userSector.SubSubSectorID;
                    cmdUserSector.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)userSector.UserTypeID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UserID", SqlDbType.Int).Value = Convert.ToInt32(UserId);
                    cmdUserSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)userSector.CreatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)userSector.CreatedBy ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)userSector.UpdatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)userSector.UpdatedBy ?? DBNull.Value;
                    cmdUserSector.Transaction = txn;
                    cmdUserSector.ExecuteNonQuery();// save recUserSector
                    cmdUserSector.Dispose();

                    string struserrole = "INSERT INTO master.recUserRole(UserID, RoleID, CreatedDate, CreatedBy) VALUES(@UserID, @RoleID, @CreatedDate, @CreatedBy)";
                    SqlCommand cmdUserRole = new SqlCommand(struserrole, conn);
                    cmdUserRole.Parameters.Add("@UserID", SqlDbType.Int).Value = Convert.ToInt32(UserId);
                    cmdUserRole.Parameters.Add("@RoleID", SqlDbType.Int).Value = (object)userRole.RoleID ?? DBNull.Value;
                    cmdUserRole.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)userRole.CreatedDate ?? DBNull.Value;
                    cmdUserRole.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)userRole.CreatedBy ?? DBNull.Value;
                    cmdUserRole.Transaction = txn;
                    cmdUserRole.ExecuteNonQuery();//save user role
                    cmdUserRole.Dispose();

                    result = true;
                    txn.Commit();
                }

                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }
        public bool SaveAndUpdate(recDebtor debtor, recDebtorSector debtorSector, recUserSector userSector, recUserRole userrole)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strUpdateDebtor = "UPDATE master.recDebtor SET  DebtorName = @DebtorName, NPWP = @NPWP, Address1 = @Address1, Address2 = @Address2, Address3 = @Address3, ZipCode = @ZipCode, RegionID = @RegionID, CityID = @CityID, Email = @Email, Phone = @Phone, Fax = @Fax, Website = @Website, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE DebtorID = @DebtorID ";
                    SqlCommand cmdDebtor = new SqlCommand(strUpdateDebtor, conn);
                    cmdDebtor.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtor.DebtorID;
                    //cmdDebtor.Parameters.Add("@DebtorCode", SqlDbType.VarChar).Value = debtor.DebtorCode;
                    cmdDebtor.Parameters.Add("@DebtorName", SqlDbType.VarChar).Value = debtor.DebtorName;
                    cmdDebtor.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = debtor.NPWP;
                    cmdDebtor.Parameters.Add("@Address1", SqlDbType.VarChar).Value = (object)debtor.Address1 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Address2", SqlDbType.VarChar).Value = (object)debtor.Address2 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Address3", SqlDbType.VarChar).Value = (object)debtor.Address3 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@ZipCode", SqlDbType.VarChar).Value = (object)debtor.ZipCode ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@RegionID", SqlDbType.Int).Value = (object)debtor.RegionID ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@CityID", SqlDbType.Int).Value = (object)debtor.CityID ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)debtor.Email ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Phone", SqlDbType.VarChar).Value = (object)debtor.Phone ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Fax", SqlDbType.VarChar).Value = (object)debtor.Fax ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Website", SqlDbType.VarChar).Value = (object)debtor.Website ?? DBNull.Value;
                    //cmdDebtor.Parameters.Add("@IsActive", SqlDbType.Bit).Value = debtor.IsActive;
                    cmdDebtor.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)debtor.UpdatedDate ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)debtor.UpdatedBy ?? DBNull.Value;
                    //cmdDebtor.Parameters.Add("@IsIndividual", SqlDbType.Bit).Value = debtor.IsIndividual;
                    cmdDebtor.Transaction = txn;
                    cmdDebtor.ExecuteNonQuery(); // update Debtor
                    cmdDebtor.Dispose();

                    string strDebtorSector = "INSERT INTO master.recDebtorSector(DebtorID, SectorID, SubSectorID, SubSubSectorID, IsActive, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@DebtorID, @SectorID, @SubSectorID, @SubSubSectorID, @IsActive, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdDebtorSector = new SqlCommand(strDebtorSector, conn);
                    cmdDebtorSector.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtorSector.DebtorID;
                    cmdDebtorSector.Parameters.Add("@SectorID", SqlDbType.Int).Value = debtorSector.SectorID;
                    cmdDebtorSector.Parameters.Add("@SubSectorID", SqlDbType.Int).Value = debtorSector.SubSectorID;
                    cmdDebtorSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = debtorSector.SubSubSectorID;
                    cmdDebtorSector.Parameters.Add("@IsActive", SqlDbType.Bit).Value = debtorSector.IsActive;
                    cmdDebtorSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)debtorSector.CreatedDate ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)debtorSector.CreatedBy ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)debtorSector.UpdatedDate ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)debtorSector.UpdatedBy ?? DBNull.Value;
                    cmdDebtorSector.Transaction = txn;
                    cmdDebtorSector.ExecuteNonQuery(); // Save DebtorSector
                    cmdDebtorSector.Dispose();

                    string strGetDebtorSectorId = " SELECT MAX(DebtorSectorID) DebtorSectorID FROM master.recDebtorSector WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetDebtorSectorId = new SqlCommand(strGetDebtorSectorId, conn);
                    cmdGetDebtorSectorId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = debtorSector.CreatedBy;
                    cmdGetDebtorSectorId.Transaction = txn;
                    var objDebtorSectorId = cmdGetDebtorSectorId.ExecuteScalar(); // Get DebtorSectorId
                    cmdGetDebtorSectorId.Dispose();

                    decimal DebtorSectorId = objDebtorSectorId == DBNull.Value ? 0m : Convert.ToDecimal(objDebtorSectorId.ToString()); // Get DebtorSectorId

                    string strCommandUserSector = "INSERT INTO master.recUserSector(RegisterSectorID, DebtorSectorID, SubSubSectorID, UserTypeID, UserID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@RegisterSectorID, @DebtorSectorID, @SubSubSectorID, @UserTypeID, @UserID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdUserSector = new SqlCommand(strCommandUserSector, conn);
                    cmdUserSector.Parameters.Add("@RegisterSectorID", SqlDbType.Int).Value = (object)userSector.RegisterSectorID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = Convert.ToInt32(DebtorSectorId);
                    cmdUserSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = userSector.SubSubSectorID;
                    cmdUserSector.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)userSector.UserTypeID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UserID", SqlDbType.Int).Value = userSector.UserID;
                    cmdUserSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)userSector.CreatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)userSector.CreatedBy ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)userSector.UpdatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)userSector.UpdatedBy ?? DBNull.Value;
                    cmdUserSector.Transaction = txn;
                    cmdUserSector.ExecuteNonQuery();// save recUserSector
                    cmdUserSector.Dispose();

                    string strUserRole = "UPDATE master.recUserRole SET RoleID = @RoleID WHERE UserID = @UserID";
                    SqlCommand cmdUserRole = new SqlCommand(strUserRole, conn);
                    cmdUserRole.Parameters.Add("@UserID", SqlDbType.Int).Value = (object)userrole.UserID ?? DBNull.Value;
                    cmdUserRole.Parameters.Add("@RoleID", SqlDbType.Int).Value = (object)userrole.RoleID ?? DBNull.Value;
                    cmdUserRole.Transaction = txn;
                    cmdUserRole.ExecuteNonQuery();// update user role menjadi debitur or debitur emiten
                    cmdUserRole.Dispose();

                    result = true;
                    txn.Commit();
                }

                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }
        public bool SaveAndUpdate(recDebtor debtor, recDebtorSector debtorSector, recUserSector userSector)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strUpdateDebtor = "UPDATE master.recDebtor SET  DebtorName = @DebtorName, NPWP = @NPWP, Address1 = @Address1, Address2 = @Address2, Address3 = @Address3, ZipCode = @ZipCode, RegionID = @RegionID, CityID = @CityID, Email = @Email, Phone = @Phone, Fax = @Fax, Website = @Website, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE DebtorID = @DebtorID ";
                    SqlCommand cmdDebtor = new SqlCommand(strUpdateDebtor, conn);
                    cmdDebtor.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtor.DebtorID;
                    //cmdDebtor.Parameters.Add("@DebtorCode", SqlDbType.VarChar).Value = debtor.DebtorCode;
                    cmdDebtor.Parameters.Add("@DebtorName", SqlDbType.VarChar).Value = debtor.DebtorName;
                    cmdDebtor.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = debtor.NPWP;
                    cmdDebtor.Parameters.Add("@Address1", SqlDbType.VarChar).Value = (object)debtor.Address1 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Address2", SqlDbType.VarChar).Value = (object)debtor.Address2 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Address3", SqlDbType.VarChar).Value = (object)debtor.Address3 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@ZipCode", SqlDbType.VarChar).Value = (object)debtor.ZipCode ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@RegionID", SqlDbType.Int).Value = (object)debtor.RegionID ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@CityID", SqlDbType.Int).Value = (object)debtor.CityID ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)debtor.Email ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Phone", SqlDbType.VarChar).Value = (object)debtor.Phone ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Fax", SqlDbType.VarChar).Value = (object)debtor.Fax ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Website", SqlDbType.VarChar).Value = (object)debtor.Website ?? DBNull.Value;
                    //cmdDebtor.Parameters.Add("@IsActive", SqlDbType.Bit).Value = debtor.IsActive;
                    cmdDebtor.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)debtor.UpdatedDate ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)debtor.UpdatedBy ?? DBNull.Value;
                    //cmdDebtor.Parameters.Add("@IsIndividual", SqlDbType.Bit).Value = debtor.IsIndividual;
                    cmdDebtor.Transaction = txn;
                    cmdDebtor.ExecuteNonQuery(); // update Debtor
                    cmdDebtor.Dispose();

                    string strDebtorSector = "INSERT INTO master.recDebtorSector(DebtorID, SectorID, SubSectorID, SubSubSectorID, IsActive, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@DebtorID, @SectorID, @SubSectorID, @SubSubSectorID, @IsActive, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdDebtorSector = new SqlCommand(strDebtorSector, conn);
                    cmdDebtorSector.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtorSector.DebtorID;
                    cmdDebtorSector.Parameters.Add("@SectorID", SqlDbType.Int).Value = debtorSector.SectorID;
                    cmdDebtorSector.Parameters.Add("@SubSectorID", SqlDbType.Int).Value = debtorSector.SubSectorID;
                    cmdDebtorSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = debtorSector.SubSubSectorID;
                    cmdDebtorSector.Parameters.Add("@IsActive", SqlDbType.Bit).Value = debtorSector.IsActive;
                    cmdDebtorSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)debtorSector.CreatedDate ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)debtorSector.CreatedBy ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)debtorSector.UpdatedDate ?? DBNull.Value;
                    cmdDebtorSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)debtorSector.UpdatedBy ?? DBNull.Value;
                    cmdDebtorSector.Transaction = txn;
                    cmdDebtorSector.ExecuteNonQuery(); // Save DebtorSector
                    cmdDebtorSector.Dispose();

                    string strGetDebtorSectorId = " SELECT MAX(DebtorSectorID) DebtorSectorID FROM master.recDebtorSector WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetDebtorSectorId = new SqlCommand(strGetDebtorSectorId, conn);
                    cmdGetDebtorSectorId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = debtorSector.CreatedBy;
                    cmdGetDebtorSectorId.Transaction = txn;
                    var objDebtorSectorId = cmdGetDebtorSectorId.ExecuteScalar(); // Get DebtorSectorId
                    cmdGetDebtorSectorId.Dispose();

                    decimal DebtorSectorId = objDebtorSectorId == DBNull.Value ? 0m : Convert.ToDecimal(objDebtorSectorId.ToString()); // Get DebtorSectorId

                    string strCommandUserSector = "INSERT INTO master.recUserSector(RegisterSectorID, DebtorSectorID, SubSubSectorID, UserTypeID, UserID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@RegisterSectorID, @DebtorSectorID, @SubSubSectorID, @UserTypeID, @UserID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdUserSector = new SqlCommand(strCommandUserSector, conn);
                    cmdUserSector.Parameters.Add("@RegisterSectorID", SqlDbType.Int).Value = (object)userSector.RegisterSectorID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = Convert.ToInt32(DebtorSectorId);
                    cmdUserSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = userSector.SubSubSectorID;
                    cmdUserSector.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)userSector.UserTypeID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UserID", SqlDbType.Int).Value = userSector.UserID;
                    cmdUserSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)userSector.CreatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)userSector.CreatedBy ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)userSector.UpdatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)userSector.UpdatedBy ?? DBNull.Value;
                    cmdUserSector.Transaction = txn;
                    cmdUserSector.ExecuteNonQuery();// save recUserSector
                    cmdUserSector.Dispose();

                    result = true;
                    txn.Commit();
                }

                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }
        public bool Save(recSectorRegistration details, recUserSector userSector, recInvoice invoice, recInvoiceDetail invoiceDetail, recBilling billing)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strCommandDetail = "INSERT INTO transactions.recSectorRegistration(SectorID, SubSectorID, SubSubSectorID, IsApproved, ApprovedDate, ApprovedBy, InvoiceNo, InvoiceDate, InvoiceValue, PaymentNo, PaymentDate, PaymentValue, RegistrationStatusID, DebtorSectorID, RegistrationID, RegistrationDate, EmisionTypeID, EmisionValue, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@SectorID, @SubSectorID, @SubSubSectorID, @IsApproved, @ApprovedDate, @ApprovedBy, @InvoiceNo, @InvoiceDate, @InvoiceValue, @PaymentNo, @PaymentDate, @PaymentValue, @RegistrationStatusID, @DebtorSectorID, @RegistrationID, @RegistrationDate, @EmisionTypeID, @EmisionValue, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdDetail = new SqlCommand(strCommandDetail, conn);
                    cmdDetail.Parameters.Add("@SectorID", SqlDbType.Int).Value = details.SectorID;
                    cmdDetail.Parameters.Add("@SubSectorID", SqlDbType.Int).Value = details.SubSectorID;
                    cmdDetail.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = details.SubSubSectorID;
                    cmdDetail.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = (object)details.IsApproved ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@ApprovedDate", SqlDbType.DateTime).Value = (object)details.ApprovedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@ApprovedBy", SqlDbType.VarChar).Value = (object)details.ApprovedBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)details.InvoiceNo ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = (object)details.InvoiceDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceValue", SqlDbType.Decimal).Value = (object)details.InvoiceValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)details.PaymentNo ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)details.PaymentDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)details.PaymentValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@RegistrationStatusID", SqlDbType.Int).Value = details.RegistrationStatusID;
                    cmdDetail.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)details.DebtorSectorID ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@RegistrationID", SqlDbType.Int).Value = details.RegistrationID;
                    cmdDetail.Parameters.Add("@RegistrationDate", SqlDbType.DateTime).Value = (object)details.RegistrationDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@EmisionTypeID", SqlDbType.SmallInt).Value = (object)details.EmisionTypeID ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@EmisionValue", SqlDbType.Money).Value = (object)details.EmisionValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)details.CreatedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)details.CreatedBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)details.UpdatedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)details.UpdatedBy ?? DBNull.Value;

                    cmdDetail.Transaction = txn;
                    cmdDetail.ExecuteNonQuery(); // Save Detail
                    cmdDetail.Dispose();

                    string strCommandGetSectorRegistrationId = " SELECT MAX(SectorRegistrationID) SectorRegistrationID FROM transactions.recSectorRegistration WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetSectorRegistrationId = new SqlCommand(strCommandGetSectorRegistrationId, conn);
                    cmdGetSectorRegistrationId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = details.CreatedBy;
                    cmdGetSectorRegistrationId.Transaction = txn;
                    var objSectorRegistrationId = cmdGetSectorRegistrationId.ExecuteScalar(); // Get SectorRegistrationId
                    cmdGetSectorRegistrationId.Dispose();

                    decimal SectorRegistrationId = objSectorRegistrationId == DBNull.Value ? 0m : Convert.ToDecimal(objSectorRegistrationId.ToString()); // Get SectorRegistrationId

                    string strCommandUserSector = "INSERT INTO master.recUserSector(RegisterSectorID, DebtorSectorID, SubSubSectorID, UserTypeID, UserID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@RegisterSectorID, @DebtorSectorID, @SubSubSectorID, @UserTypeID, @UserID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdUserSector = new SqlCommand(strCommandUserSector, conn);
                    cmdUserSector.Parameters.Add("@RegisterSectorID", SqlDbType.Int).Value = Convert.ToInt32(SectorRegistrationId);
                    cmdUserSector.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)userSector.DebtorSectorID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = details.SubSubSectorID;
                    cmdUserSector.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)userSector.UserTypeID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UserID", SqlDbType.Int).Value = (object)userSector.UserID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)userSector.CreatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)userSector.CreatedBy ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)userSector.UpdatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)userSector.UpdatedBy ?? DBNull.Value;
                    cmdUserSector.Transaction = txn;
                    cmdUserSector.ExecuteNonQuery();
                    cmdUserSector.Dispose();

                    string strInvoice = "INSERT INTO transactions.recInvoice(InvoiceNo, InvoiceDate, InvoiceValue, BankID, BeSpelled, PaymentNo, PaymentDate, PaymentValue, IsHapusBuku, IsHapusTagih, DebtorSectorID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@InvoiceNo, @InvoiceDate, @InvoiceValue, @BankID, @BeSpelled, @PaymentNo, @PaymentDate, @PaymentValue, @IsHapusBuku, @IsHapusTagih, @DebtorSectorID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdInvoice = new SqlCommand(strInvoice, conn);
                    cmdInvoice.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoice.InvoiceNo;
                    //cmdInvoice.Parameters.Add("@PeriodYear", SqlDbType.Int).Value = invoice.PeriodYear;
                    //cmdInvoice.Parameters.Add("@PeriodMonth", SqlDbType.SmallInt).Value = invoice.PeriodMonth;
                    //cmdInvoice.Parameters.Add("@TransDate", SqlDbType.DateTime).Value = invoice.TransDate;
                    cmdInvoice.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = invoice.InvoiceDate;
                    cmdInvoice.Parameters.Add("@InvoiceValue", SqlDbType.Money).Value = (object)invoice.InvoiceValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@BankID", SqlDbType.SmallInt).Value = invoice.BankID;
                    cmdInvoice.Parameters.Add("@BeSpelled", SqlDbType.VarChar).Value = (object)invoice.BeSpelled ?? DBNull.Value;
                    //cmdInvoice.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)invoice.ForPayment ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)invoice.PaymentNo ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)invoice.PaymentDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)invoice.PaymentValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@IsHapusBuku", SqlDbType.Bit).Value = (object)invoice.IsHapusBuku ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@IsHapusTagih", SqlDbType.Bit).Value = (object)invoice.IsHapusTagih ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)invoice.DebtorSectorID ?? -1;
                    //cmdInvoice.Parameters.Add("@PostingDate", SqlDbType.DateTime).Value = (object)invoice.PostingDate ?? DBNull.Value;
                    //cmdInvoice.Parameters.Add("@PostingBy", SqlDbType.VarChar).Value = (object)invoice.PostingBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)invoice.CreatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)invoice.CreatedBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)invoice.UpdatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoice.UpdatedBy ?? DBNull.Value;
                    cmdInvoice.Transaction = txn;
                    cmdInvoice.ExecuteNonQuery();//save invoice
                    cmdInvoice.Dispose();

                    string strInvoiceDetail = "INSERT INTO transactions.recInvoiceDetail(InvoiceNo, Value, TransTypeID, TariffID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@InvoiceNo, @Value, @TransTypeID, @TariffID, @CreateDate, @CreateBy, @UpdateDate, @UpdateBy)";
                    SqlCommand cmdInvoiceDetail = new SqlCommand(strInvoiceDetail, conn);
                    //cmdInvoiceDetail.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = invoiceDetail.InvoiceDetailID;
                    cmdInvoiceDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoiceDetail.InvoiceNo;
                    cmdInvoiceDetail.Parameters.Add("@Value", SqlDbType.Money).Value = invoiceDetail.Value;
                    cmdInvoiceDetail.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = invoiceDetail.TransTypeID;
                    cmdInvoiceDetail.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)invoiceDetail.TariffID ?? DBNull.Value;
                    //cmdInvoiceDetail.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = (object)invoiceDetail.CreatedDate ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = (object)invoiceDetail.CreatedBy ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)invoiceDetail.UpdatedDate ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)invoiceDetail.UpdatedBy ?? DBNull.Value;
                    cmdInvoiceDetail.Transaction = txn;
                    cmdInvoiceDetail.ExecuteNonQuery();//save invoice detail
                    cmdInvoiceDetail.Dispose();

                    string strGetInvoiceDetailId = " SELECT MAX(InvoiceDetailID) InvoiceDetailID FROM transactions.recInvoiceDetail WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetInvoiceDetailId = new SqlCommand(strGetInvoiceDetailId, conn);
                    cmdGetInvoiceDetailId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = invoiceDetail.CreatedBy;
                    cmdGetInvoiceDetailId.Transaction = txn;
                    var objInvoiceDetailId = cmdGetInvoiceDetailId.ExecuteScalar(); // Get SectorRegistrationId
                    cmdGetInvoiceDetailId.Dispose();

                    decimal InvoiceDetailId = objInvoiceDetailId == DBNull.Value ? 0m : Convert.ToDecimal(objInvoiceDetailId.ToString()); // Get InvoiceDetailId

                    string strBilling = "INSERT INTO transactions.recBilling(BillingNo, DebtorSectorID, TransTypeID, TariffID, Value, InvoiceNo, InvoiceDetailID, BillingDate,CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, DueDate) VALUES(@BillingNo, @DebtorSectorID, @TransTypeID, @TariffID, @Value, @InvoiceNo, @InvoiceDetailID, @BillingDate, @CreateDate, @CreateBy, @UpdateDate, @UpdateBy, @DueDate)";
                    SqlCommand cmdBilling = new SqlCommand(strBilling, conn);
                    cmdBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = billing.DebtorSectorID;
                    cmdBilling.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = billing.TransTypeID;
                    cmdBilling.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)billing.TariffID ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@Value", SqlDbType.Money).Value = billing.Value;
                    cmdBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)billing.InvoiceNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = (object)InvoiceDetailId ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@BillingDate", SqlDbType.DateTime).Value = (object)billing.BillingDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = (object)billing.CreatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = (object)billing.CreatedBy ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)billing.UpdatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)billing.UpdatedBy ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = (object)billing.DueDate ?? DBNull.Value;
                    cmdBilling.Transaction = txn;
                    cmdBilling.ExecuteNonQuery();//save billing
                    cmdBilling.Dispose();

                    string strUpdateInvoiceDetail = "UPDATE transactions.recInvoiceDetail SET BillingNo = @BillingNo ,UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy where InvoiceNo = @InvoiceNo ";
                    SqlCommand cmdUpdateInvoiceDetail = new SqlCommand(strUpdateInvoiceDetail, conn);
                    cmdUpdateInvoiceDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoiceDetail.InvoiceNo;
                    cmdUpdateInvoiceDetail.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdUpdateInvoiceDetail.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = DateTime.Now;
                    cmdUpdateInvoiceDetail.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoiceDetail.CreatedBy ?? DBNull.Value;
                    cmdUpdateInvoiceDetail.Transaction = txn;
                    cmdUpdateInvoiceDetail.ExecuteNonQuery();//update invoicedetail
                    cmdUpdateInvoiceDetail.Dispose();

                    result = true;
                    txn.Commit();
                }

                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }
        public bool Save(recSectorRegistration details, recUser user, recUserSector userSector, recInvoice invoice, recInvoiceDetail invoiceDetail, recBilling billing, recUserRole userRole)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strCommandDetail = "INSERT INTO transactions.recSectorRegistration(SectorID, SubSectorID, SubSubSectorID, IsApproved, ApprovedDate, ApprovedBy, InvoiceNo, InvoiceDate, InvoiceValue, PaymentNo, PaymentDate, PaymentValue, RegistrationStatusID, DebtorSectorID, RegistrationID, RegistrationDate, EmisionTypeID, EmisionValue, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@SectorID, @SubSectorID, @SubSubSectorID, @IsApproved, @ApprovedDate, @ApprovedBy, @InvoiceNo, @InvoiceDate, @InvoiceValue, @PaymentNo, @PaymentDate, @PaymentValue, @RegistrationStatusID, @DebtorSectorID, @RegistrationID, @RegistrationDate, @EmisionTypeID, @EmisionValue, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdDetail = new SqlCommand(strCommandDetail, conn);
                    cmdDetail.Parameters.Add("@SectorID", SqlDbType.Int).Value = details.SectorID;
                    cmdDetail.Parameters.Add("@SubSectorID", SqlDbType.Int).Value = details.SubSectorID;
                    cmdDetail.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = details.SubSubSectorID;
                    cmdDetail.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = (object)details.IsApproved ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@ApprovedDate", SqlDbType.DateTime).Value = (object)details.ApprovedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@ApprovedBy", SqlDbType.VarChar).Value = (object)details.ApprovedBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)details.InvoiceNo ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = (object)details.InvoiceDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceValue", SqlDbType.Decimal).Value = (object)details.InvoiceValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)details.PaymentNo ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)details.PaymentDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)details.PaymentValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@RegistrationStatusID", SqlDbType.Int).Value = details.RegistrationStatusID;
                    cmdDetail.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)details.DebtorSectorID ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@RegistrationID", SqlDbType.Int).Value = details.RegistrationID;
                    cmdDetail.Parameters.Add("@RegistrationDate", SqlDbType.DateTime).Value = (object)details.RegistrationDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@EmisionTypeID", SqlDbType.SmallInt).Value = (object)details.EmisionTypeID ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@EmisionValue", SqlDbType.Money).Value = (object)details.EmisionValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)details.CreatedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)details.CreatedBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)details.UpdatedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)details.UpdatedBy ?? DBNull.Value;

                    cmdDetail.Transaction = txn;
                    cmdDetail.ExecuteNonQuery(); // Save Detail
                    cmdDetail.Dispose();

                    string strCommandUser = "INSERT INTO master.recUser(UserCode, UserName, Email, Password, IsActive, Mobile, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate, UserTypeID) VALUES(@UserCode, @UserName, @Email, @Password, @IsActive, @Mobile, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate, @UserTypeID)";
                    SqlCommand cmdUser = new SqlCommand(strCommandUser, conn);
                    cmdUser.Parameters.Add("@UserCode", SqlDbType.VarChar).Value = user.UserCode;
                    cmdUser.Parameters.Add("@UserName", SqlDbType.VarChar).Value = (object)user.UserName ?? DBNull.Value;
                    cmdUser.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)user.Email ?? DBNull.Value;
                    cmdUser.Parameters.Add("@Password", SqlDbType.VarChar).Value = user.Password;
                    cmdUser.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)user.IsActive ?? DBNull.Value;
                    cmdUser.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = (object)user.Mobile ?? DBNull.Value;
                    cmdUser.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)user.CreatedBy ?? DBNull.Value;
                    cmdUser.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)user.CreatedDate ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)user.UpdatedBy ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)user.UpdatedDate ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)user.UserTypeID ?? DBNull.Value;
                    cmdUser.Transaction = txn;
                    cmdUser.ExecuteNonQuery();
                    cmdUser.Dispose();

                    string strCommandGetUserId = " SELECT MAX(UserID) UserID FROM master.recUser WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetUserId = new SqlCommand(strCommandGetUserId, conn);
                    cmdGetUserId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = user.CreatedBy;
                    cmdGetUserId.Transaction = txn;
                    var objUserId = cmdGetUserId.ExecuteScalar(); // Get User Id
                    cmdGetUserId.Dispose();

                    decimal UserId = objUserId == DBNull.Value ? 0m : Convert.ToDecimal(objUserId.ToString()); // Get User Id

                    string strCommandGetSectorRegistrationId = " SELECT MAX(SectorRegistrationID) SectorRegistrationID FROM transactions.recSectorRegistration WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetSectorRegistrationId = new SqlCommand(strCommandGetSectorRegistrationId, conn);
                    cmdGetSectorRegistrationId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = details.CreatedBy;
                    cmdGetSectorRegistrationId.Transaction = txn;
                    var objSectorRegistrationId = cmdGetSectorRegistrationId.ExecuteScalar(); // Get SectorRegistrationId
                    cmdGetSectorRegistrationId.Dispose();

                    decimal SectorRegistrationId = objSectorRegistrationId == DBNull.Value ? 0m : Convert.ToDecimal(objSectorRegistrationId.ToString()); // Get SectorRegistrationId

                    string strCommandUserSector = "INSERT INTO master.recUserSector(RegisterSectorID, DebtorSectorID, SubSubSectorID, UserTypeID, UserID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@RegisterSectorID, @DebtorSectorID, @SubSubSectorID, @UserTypeID, @UserID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdUserSector = new SqlCommand(strCommandUserSector, conn);
                    cmdUserSector.Parameters.Add("@RegisterSectorID", SqlDbType.Int).Value = Convert.ToInt32(SectorRegistrationId);
                    cmdUserSector.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)userSector.DebtorSectorID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = details.SubSubSectorID;
                    cmdUserSector.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)userSector.UserTypeID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UserID", SqlDbType.Int).Value = Convert.ToInt32(UserId);
                    cmdUserSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)userSector.CreatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)userSector.CreatedBy ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)userSector.UpdatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)userSector.UpdatedBy ?? DBNull.Value;
                    cmdUserSector.Transaction = txn;
                    cmdUserSector.ExecuteNonQuery();
                    cmdUserSector.Dispose();

                    string strInvoice = "INSERT INTO transactions.recInvoice(InvoiceNo, InvoiceDate, InvoiceValue, BankID, BeSpelled, PaymentNo, PaymentDate, PaymentValue, IsHapusBuku, IsHapusTagih, DebtorSectorID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@InvoiceNo, @InvoiceDate, @InvoiceValue, @BankID, @BeSpelled, @PaymentNo, @PaymentDate, @PaymentValue, @IsHapusBuku, @IsHapusTagih, @DebtorSectorID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdInvoice = new SqlCommand(strInvoice, conn);
                    cmdInvoice.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoice.InvoiceNo;
                    //cmdInvoice.Parameters.Add("@PeriodYear", SqlDbType.Int).Value = invoice.PeriodYear;
                    //cmdInvoice.Parameters.Add("@PeriodMonth", SqlDbType.SmallInt).Value = invoice.PeriodMonth;
                    //cmdInvoice.Parameters.Add("@TransDate", SqlDbType.DateTime).Value = invoice.TransDate;
                    cmdInvoice.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = invoice.InvoiceDate;
                    cmdInvoice.Parameters.Add("@InvoiceValue", SqlDbType.Money).Value = (object)invoice.InvoiceValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@BankID", SqlDbType.SmallInt).Value = invoice.BankID;
                    cmdInvoice.Parameters.Add("@BeSpelled", SqlDbType.VarChar).Value = (object)invoice.BeSpelled ?? DBNull.Value;
                    //cmdInvoice.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)invoice.ForPayment ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)invoice.PaymentNo ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)invoice.PaymentDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)invoice.PaymentValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@IsHapusBuku", SqlDbType.Bit).Value = (object)invoice.IsHapusBuku ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@IsHapusTagih", SqlDbType.Bit).Value = (object)invoice.IsHapusTagih ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)invoice.DebtorSectorID ?? -1;
                    //cmdInvoice.Parameters.Add("@PostingDate", SqlDbType.DateTime).Value = (object)invoice.PostingDate ?? DBNull.Value;
                    //cmdInvoice.Parameters.Add("@PostingBy", SqlDbType.VarChar).Value = (object)invoice.PostingBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)invoice.CreatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)invoice.CreatedBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)invoice.UpdatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoice.UpdatedBy ?? DBNull.Value;
                    cmdInvoice.Transaction = txn;
                    cmdInvoice.ExecuteNonQuery();//save invoice
                    cmdInvoice.Dispose();

                    string strInvoiceDetail = "INSERT INTO transactions.recInvoiceDetail(InvoiceNo, Value, TransTypeID, TariffID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@InvoiceNo, @Value, @TransTypeID, @TariffID, @CreateDate, @CreateBy, @UpdateDate, @UpdateBy)";
                    SqlCommand cmdInvoiceDetail = new SqlCommand(strInvoiceDetail, conn);
                    //cmdInvoiceDetail.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = invoiceDetail.InvoiceDetailID;
                    cmdInvoiceDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoiceDetail.InvoiceNo;
                    cmdInvoiceDetail.Parameters.Add("@Value", SqlDbType.Money).Value = invoiceDetail.Value;
                    cmdInvoiceDetail.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = invoiceDetail.TransTypeID;
                    cmdInvoiceDetail.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)invoiceDetail.TariffID ?? DBNull.Value;
                    //cmdInvoiceDetail.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = (object)invoiceDetail.CreatedDate ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = (object)invoiceDetail.CreatedBy ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)invoiceDetail.UpdatedDate ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)invoiceDetail.UpdatedBy ?? DBNull.Value;
                    cmdInvoiceDetail.Transaction = txn;
                    cmdInvoiceDetail.ExecuteNonQuery();//save invoice detail
                    cmdInvoiceDetail.Dispose();

                    string strGetInvoiceDetailId = " SELECT MAX(InvoiceDetailID) InvoiceDetailID FROM transactions.recInvoiceDetail WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetInvoiceDetailId = new SqlCommand(strGetInvoiceDetailId, conn);
                    cmdGetInvoiceDetailId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = invoiceDetail.CreatedBy;
                    cmdGetInvoiceDetailId.Transaction = txn;
                    var objInvoiceDetailId = cmdGetInvoiceDetailId.ExecuteScalar(); // Get SectorRegistrationId
                    cmdGetInvoiceDetailId.Dispose();

                    decimal InvoiceDetailId = objInvoiceDetailId == DBNull.Value ? 0m : Convert.ToDecimal(objInvoiceDetailId.ToString()); // Get InvoiceDetailId

                    string strBilling = "INSERT INTO transactions.recBilling(BillingNo, DebtorSectorID, TransTypeID, TariffID, Value, InvoiceNo, InvoiceDetailID, BillingDate,CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, DueDate) VALUES(@BillingNo, @DebtorSectorID, @TransTypeID, @TariffID, @Value, @InvoiceNo, @InvoiceDetailID, @BillingDate, @CreateDate, @CreateBy, @UpdateDate, @UpdateBy, @DueDate)";
                    SqlCommand cmdBilling = new SqlCommand(strBilling, conn);
                    cmdBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = billing.DebtorSectorID;
                    cmdBilling.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = billing.TransTypeID;
                    cmdBilling.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)billing.TariffID ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@Value", SqlDbType.Money).Value = billing.Value;
                    cmdBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)billing.InvoiceNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = (object)InvoiceDetailId ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@BillingDate", SqlDbType.DateTime).Value = (object)billing.BillingDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = (object)billing.CreatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = (object)billing.CreatedBy ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)billing.UpdatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)billing.UpdatedBy ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = (object)billing.DueDate ?? DBNull.Value;
                    cmdBilling.Transaction = txn;
                    cmdBilling.ExecuteNonQuery();//save billing
                    cmdBilling.Dispose();

                    string strUpdateInvoiceDetail = "UPDATE transactions.recInvoiceDetail SET BillingNo = @BillingNo ,UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy where InvoiceNo = @InvoiceNo ";
                    SqlCommand cmdUpdateInvoiceDetail = new SqlCommand(strUpdateInvoiceDetail, conn);
                    cmdUpdateInvoiceDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoiceDetail.InvoiceNo;
                    cmdUpdateInvoiceDetail.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdUpdateInvoiceDetail.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = DateTime.Now;
                    cmdUpdateInvoiceDetail.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoiceDetail.CreatedBy ?? DBNull.Value;
                    cmdUpdateInvoiceDetail.Transaction = txn;
                    cmdUpdateInvoiceDetail.ExecuteNonQuery();//update invoicedetail
                    cmdUpdateInvoiceDetail.Dispose();

                    string struserrole = "INSERT INTO master.recUserRole(UserID, RoleID, CreatedDate, CreatedBy) VALUES(@UserID, @RoleID, @CreatedDate, @CreatedBy)";
                    SqlCommand cmdUserRole = new SqlCommand(struserrole, conn);
                    cmdUserRole.Parameters.Add("@UserID", SqlDbType.Int).Value = Convert.ToInt32(UserId);
                    cmdUserRole.Parameters.Add("@RoleID", SqlDbType.Int).Value = (object)userRole.RoleID ?? DBNull.Value;
                    cmdUserRole.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)userRole.CreatedDate ?? DBNull.Value;
                    cmdUserRole.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)userRole.CreatedBy ?? DBNull.Value;
                    cmdUserRole.Transaction = txn;
                    cmdUserRole.ExecuteNonQuery();//save user role
                    cmdUserRole.Dispose();

                    result = true;
                    txn.Commit();
                }

                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }
        public recRegistration GetByUserId(int userid)
        {
            recRegistration t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                StringBuilder sbQuery = new StringBuilder();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    sbQuery.AppendFormat("select top 1 trr.RegistrationID from transactions.recRegistration trr {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join transactions.recSectorRegistration trsr {0}", Environment.NewLine);
                    sbQuery.AppendFormat("on trsr.RegistrationID=trr.RegistrationID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recUserSector mru on mru.RegisterSectorID=trsr.SectorRegistrationID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("where mru.UserID = @UserId {0}", Environment.NewLine);
                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = userid;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recRegistration(true);
                                t.RegistrationID = dr[recRegistration.Fields.RegistrationID] == DBNull.Value ? 0 : int.Parse(dr[recRegistration.Fields.RegistrationID].ToString());
                                //t.recUserSector.UserTypeID = dr[recUserSector.Fields.UserTypeID] == DBNull.Value ? 0 : int.Parse(dr[recUserSector.Fields.UserTypeID].ToString());

                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }
        public bool SaveAndUpdate(recRegistration domain, recSectorRegistration details, recUserSector userSector, recInvoice invoice, recInvoiceDetail invoiceDetail, recBilling billing)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strRegistration = "UPDATE transactions.recRegistration SET PreDebtorName = @PreDebtorName, NPWP = @NPWP, Address1 = @Address1, Address2 = @Address2, Address3 = @Address3, RegionID = @RegionID, CityID = @CityID, ZipCode = @ZipCode, Email = @Email, Phone = @Phone, Fax = @Fax, Website = @Website, UpdatedBy = @UpdatedBy, UpdatedDate = @UpdatedDate WHERE RegistrationID = @RegistrationID";
                    SqlCommand cmdRegistration = new SqlCommand(strRegistration, conn);
                    cmdRegistration.Parameters.Add("@RegistrationID", SqlDbType.Int).Value = domain.RegistrationID;
                    cmdRegistration.Parameters.Add("@PreDebtorName", SqlDbType.VarChar).Value = domain.PreDebtorName;
                    cmdRegistration.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = domain.NPWP;
                    cmdRegistration.Parameters.Add("@Address1", SqlDbType.VarChar).Value = (object)domain.Address1 ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@Address2", SqlDbType.VarChar).Value = (object)domain.Address2 ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@Address3", SqlDbType.VarChar).Value = (object)domain.Address3 ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@RegionID", SqlDbType.Int).Value = (object)domain.RegionID ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@CityID", SqlDbType.Int).Value = (object)domain.CityID ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@ZipCode", SqlDbType.VarChar).Value = (object)domain.ZipCode ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)domain.Email ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@Phone", SqlDbType.VarChar).Value = (object)domain.Phone ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@Fax", SqlDbType.VarChar).Value = (object)domain.Fax ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@Website", SqlDbType.VarChar).Value = (object)domain.Website ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    //cmdRegistration.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)domain.IsActive ?? DBNull.Value;
                    //cmdRegistration.Parameters.Add("@IsIndividual", SqlDbType.Bit).Value = (object)domain.IsIndividual ?? DBNull.Value;
                    cmdRegistration.Transaction = txn;
                    cmdRegistration.ExecuteNonQuery(); // update registration
                    cmdRegistration.Dispose();

                    string strCommandDetail = "INSERT INTO transactions.recSectorRegistration(SectorID, SubSectorID, SubSubSectorID, IsApproved, ApprovedDate, ApprovedBy, InvoiceNo, InvoiceDate, InvoiceValue, PaymentNo, PaymentDate, PaymentValue, RegistrationStatusID, DebtorSectorID, RegistrationID, RegistrationDate, EmisionTypeID, EmisionValue, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, RegistrationEmisionTypeID) VALUES(@SectorID, @SubSectorID, @SubSubSectorID, @IsApproved, @ApprovedDate, @ApprovedBy, @InvoiceNo, @InvoiceDate, @InvoiceValue, @PaymentNo, @PaymentDate, @PaymentValue, @RegistrationStatusID, @DebtorSectorID, @RegistrationID, @RegistrationDate, @EmisionTypeID, @EmisionValue, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy, @RegistrationEmisionTypeID)";
                    SqlCommand cmdDetail = new SqlCommand(strCommandDetail, conn);
                    cmdDetail.Parameters.Add("@SectorID", SqlDbType.Int).Value = details.SectorID;
                    cmdDetail.Parameters.Add("@SubSectorID", SqlDbType.Int).Value = details.SubSectorID;
                    cmdDetail.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = details.SubSubSectorID;
                    cmdDetail.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = (object)details.IsApproved ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@ApprovedDate", SqlDbType.DateTime).Value = (object)details.ApprovedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@ApprovedBy", SqlDbType.VarChar).Value = (object)details.ApprovedBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)details.InvoiceNo ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = (object)details.InvoiceDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceValue", SqlDbType.Decimal).Value = (object)details.InvoiceValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)details.PaymentNo ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)details.PaymentDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)details.PaymentValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@RegistrationStatusID", SqlDbType.Int).Value = details.RegistrationStatusID;
                    cmdDetail.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)details.DebtorSectorID ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@RegistrationID", SqlDbType.Int).Value = details.RegistrationID;
                    cmdDetail.Parameters.Add("@RegistrationDate", SqlDbType.DateTime).Value = (object)details.RegistrationDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@EmisionTypeID", SqlDbType.SmallInt).Value = (object)details.EmisionTypeID ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@EmisionValue", SqlDbType.Money).Value = (object)details.EmisionValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)details.CreatedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)details.CreatedBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)details.UpdatedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)details.UpdatedBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@RegistrationEmisionTypeID", SqlDbType.SmallInt).Value = (object)details.RegistrationEmisionTypeID ?? DBNull.Value;

                    cmdDetail.Transaction = txn;
                    cmdDetail.ExecuteNonQuery(); // Save Detail
                    cmdDetail.Dispose();

                    string strCommandGetSectorRegistrationId = " SELECT MAX(SectorRegistrationID) SectorRegistrationID FROM transactions.recSectorRegistration WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetSectorRegistrationId = new SqlCommand(strCommandGetSectorRegistrationId, conn);
                    cmdGetSectorRegistrationId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = details.CreatedBy;
                    cmdGetSectorRegistrationId.Transaction = txn;
                    var objSectorRegistrationId = cmdGetSectorRegistrationId.ExecuteScalar(); // Get SectorRegistrationId
                    cmdGetSectorRegistrationId.Dispose();

                    decimal SectorRegistrationId = objSectorRegistrationId == DBNull.Value ? 0m : Convert.ToDecimal(objSectorRegistrationId.ToString()); // Get SectorRegistrationId

                    string strCommandUserSector = "INSERT INTO master.recUserSector(RegisterSectorID, DebtorSectorID, SubSubSectorID, UserTypeID, UserID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@RegisterSectorID, @DebtorSectorID, @SubSubSectorID, @UserTypeID, @UserID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdUserSector = new SqlCommand(strCommandUserSector, conn);
                    cmdUserSector.Parameters.Add("@RegisterSectorID", SqlDbType.Int).Value = Convert.ToInt32(SectorRegistrationId);
                    cmdUserSector.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)userSector.DebtorSectorID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = details.SubSubSectorID;
                    cmdUserSector.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)userSector.UserTypeID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UserID", SqlDbType.Int).Value = (object)userSector.UserID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)userSector.CreatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)userSector.CreatedBy ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)userSector.UpdatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)userSector.UpdatedBy ?? DBNull.Value;
                    cmdUserSector.Transaction = txn;
                    cmdUserSector.ExecuteNonQuery();
                    cmdUserSector.Dispose();

                    string strInvoice = "INSERT INTO transactions.recInvoice(InvoiceNo, InvoiceDate, InvoiceValue, BankID, BeSpelled, PaymentNo, PaymentDate, PaymentValue, IsHapusBuku, IsHapusTagih, DebtorSectorID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, Balance) VALUES(@InvoiceNo, @InvoiceDate, @InvoiceValue, @BankID, @BeSpelled, @PaymentNo, @PaymentDate, @PaymentValue, @IsHapusBuku, @IsHapusTagih, @DebtorSectorID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy, @Balance)";
                    SqlCommand cmdInvoice = new SqlCommand(strInvoice, conn);
                    cmdInvoice.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoice.InvoiceNo;
                    //cmdInvoice.Parameters.Add("@PeriodYear", SqlDbType.Int).Value = invoice.PeriodYear;
                    //cmdInvoice.Parameters.Add("@PeriodMonth", SqlDbType.SmallInt).Value = invoice.PeriodMonth;
                    //cmdInvoice.Parameters.Add("@TransDate", SqlDbType.DateTime).Value = invoice.TransDate;
                    cmdInvoice.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = invoice.InvoiceDate;
                    cmdInvoice.Parameters.Add("@InvoiceValue", SqlDbType.Money).Value = (object)invoice.InvoiceValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@BankID", SqlDbType.SmallInt).Value = invoice.BankID;
                    cmdInvoice.Parameters.Add("@BeSpelled", SqlDbType.VarChar).Value = (object)invoice.BeSpelled ?? DBNull.Value;
                    //cmdInvoice.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)invoice.ForPayment ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)invoice.PaymentNo ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)invoice.PaymentDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)invoice.PaymentValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@IsHapusBuku", SqlDbType.Bit).Value = (object)invoice.IsHapusBuku ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@IsHapusTagih", SqlDbType.Bit).Value = (object)invoice.IsHapusTagih ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)invoice.DebtorSectorID ?? -1;
                    //cmdInvoice.Parameters.Add("@PostingDate", SqlDbType.DateTime).Value = (object)invoice.PostingDate ?? DBNull.Value;
                    //cmdInvoice.Parameters.Add("@PostingBy", SqlDbType.VarChar).Value = (object)invoice.PostingBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)invoice.CreatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)invoice.CreatedBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)invoice.UpdatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoice.UpdatedBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@Balance", SqlDbType.Money).Value = (object)invoice.Balance ?? DBNull.Value;
                    cmdInvoice.Transaction = txn;
                    cmdInvoice.ExecuteNonQuery();//save invoice
                    cmdInvoice.Dispose();

                    string strInvoiceDetail = "INSERT INTO transactions.recInvoiceDetail(InvoiceNo, Value, TransTypeID, TariffID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, ForPayment, DueDate) VALUES(@InvoiceNo, @Value, @TransTypeID, @TariffID, @CreateDate, @CreateBy, @UpdateDate, @UpdateBy, @ForPayment, @DueDate)";
                    SqlCommand cmdInvoiceDetail = new SqlCommand(strInvoiceDetail, conn);
                    //cmdInvoiceDetail.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = invoiceDetail.InvoiceDetailID;
                    cmdInvoiceDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoiceDetail.InvoiceNo;
                    cmdInvoiceDetail.Parameters.Add("@Value", SqlDbType.Money).Value = invoiceDetail.Value;
                    cmdInvoiceDetail.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = invoiceDetail.TransTypeID;
                    cmdInvoiceDetail.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)invoiceDetail.TariffID ?? DBNull.Value;
                    //cmdInvoiceDetail.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = (object)invoiceDetail.CreatedDate ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = (object)invoiceDetail.CreatedBy ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)invoiceDetail.UpdatedDate ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)invoiceDetail.UpdatedBy ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)invoiceDetail.ForPayment ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = (object)invoiceDetail.DueDate ?? DBNull.Value;
                    cmdInvoiceDetail.Transaction = txn;
                    cmdInvoiceDetail.ExecuteNonQuery();//save invoice detail
                    cmdInvoiceDetail.Dispose();

                    string strGetInvoiceDetailId = " SELECT MAX(InvoiceDetailID) InvoiceDetailID FROM transactions.recInvoiceDetail WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetInvoiceDetailId = new SqlCommand(strGetInvoiceDetailId, conn);
                    cmdGetInvoiceDetailId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = invoiceDetail.CreatedBy;
                    cmdGetInvoiceDetailId.Transaction = txn;
                    var objInvoiceDetailId = cmdGetInvoiceDetailId.ExecuteScalar(); // Get SectorRegistrationId
                    cmdGetInvoiceDetailId.Dispose();

                    decimal InvoiceDetailId = objInvoiceDetailId == DBNull.Value ? 0m : Convert.ToDecimal(objInvoiceDetailId.ToString()); // Get InvoiceDetailId

                    string strBilling = "INSERT INTO transactions.recBilling(BillingNo, DebtorSectorID, TransTypeID, TariffID, Value, InvoiceNo, InvoiceDetailID, BillingDate,CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, DueDate, ForPayment, Balance) VALUES(@BillingNo, @DebtorSectorID, @TransTypeID, @TariffID, @Value, @InvoiceNo, @InvoiceDetailID, @BillingDate, @CreateDate, @CreateBy, @UpdateDate, @UpdateBy, @DueDate, @ForPayment, @Balance)";
                    SqlCommand cmdBilling = new SqlCommand(strBilling, conn);
                    cmdBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = billing.DebtorSectorID;
                    cmdBilling.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = billing.TransTypeID;
                    cmdBilling.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)billing.TariffID ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@Value", SqlDbType.Money).Value = billing.Value;
                    cmdBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)billing.InvoiceNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = (object)InvoiceDetailId ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@BillingDate", SqlDbType.DateTime).Value = (object)billing.BillingDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = (object)billing.CreatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = (object)billing.CreatedBy ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)billing.UpdatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)billing.UpdatedBy ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = (object)billing.DueDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)billing.ForPayment ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@Balance", SqlDbType.Money).Value = (object)billing.Balance ?? DBNull.Value;
                    cmdBilling.Transaction = txn;
                    cmdBilling.ExecuteNonQuery();//save billing
                    cmdBilling.Dispose();

                    string strUpdateInvoiceDetail = "UPDATE transactions.recInvoiceDetail SET BillingNo = @BillingNo ,UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy where InvoiceNo = @InvoiceNo ";
                    SqlCommand cmdUpdateInvoiceDetail = new SqlCommand(strUpdateInvoiceDetail, conn);
                    cmdUpdateInvoiceDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoiceDetail.InvoiceNo;
                    cmdUpdateInvoiceDetail.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdUpdateInvoiceDetail.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = DateTime.Now;
                    cmdUpdateInvoiceDetail.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoiceDetail.CreatedBy ?? DBNull.Value;
                    cmdUpdateInvoiceDetail.Transaction = txn;
                    cmdUpdateInvoiceDetail.ExecuteNonQuery();//update invoicedetail
                    cmdUpdateInvoiceDetail.Dispose();

                    result = true;
                    txn.Commit();
                }

                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }
        public bool SaveAndUpdate(recRegistration domain, recSectorRegistration details, recUser user, recUserSector userSector, recInvoice invoice, recInvoiceDetail invoiceDetail, recBilling billing, recUserRole userRole)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strRegistration = "UPDATE transactions.recRegistration SET PreDebtorName = @PreDebtorName, NPWP = @NPWP, Address1 = @Address1, Address2 = @Address2, Address3 = @Address3, RegionID = @RegionID, CityID = @CityID, ZipCode = @ZipCode, Email = @Email, Phone = @Phone, Fax = @Fax, Website = @Website, UpdatedBy = @UpdatedBy, UpdatedDate = @UpdatedDate WHERE RegistrationID = @RegistrationID";
                    SqlCommand cmdRegistration = new SqlCommand(strRegistration, conn);
                    cmdRegistration.Parameters.Add("@RegistrationID", SqlDbType.Int).Value = domain.RegistrationID;
                    cmdRegistration.Parameters.Add("@PreDebtorName", SqlDbType.VarChar).Value = domain.PreDebtorName;
                    cmdRegistration.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = domain.NPWP;
                    cmdRegistration.Parameters.Add("@Address1", SqlDbType.VarChar).Value = (object)domain.Address1 ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@Address2", SqlDbType.VarChar).Value = (object)domain.Address2 ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@Address3", SqlDbType.VarChar).Value = (object)domain.Address3 ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@RegionID", SqlDbType.Int).Value = (object)domain.RegionID ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@CityID", SqlDbType.Int).Value = (object)domain.CityID ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@ZipCode", SqlDbType.VarChar).Value = (object)domain.ZipCode ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)domain.Email ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@Phone", SqlDbType.VarChar).Value = (object)domain.Phone ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@Fax", SqlDbType.VarChar).Value = (object)domain.Fax ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@Website", SqlDbType.VarChar).Value = (object)domain.Website ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmdRegistration.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    //cmdRegistration.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)domain.IsActive ?? DBNull.Value;
                    //cmdRegistration.Parameters.Add("@IsIndividual", SqlDbType.Bit).Value = (object)domain.IsIndividual ?? DBNull.Value;
                    cmdRegistration.Transaction = txn;
                    cmdRegistration.ExecuteNonQuery(); // update registration
                    cmdRegistration.Dispose();

                    string strCommandDetail = "INSERT INTO transactions.recSectorRegistration(SectorID, SubSectorID, SubSubSectorID, IsApproved, ApprovedDate, ApprovedBy, InvoiceNo, InvoiceDate, InvoiceValue, PaymentNo, PaymentDate, PaymentValue, RegistrationStatusID, DebtorSectorID, RegistrationID, RegistrationDate, EmisionTypeID, EmisionValue, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, RegistrationEmisionTypeID) VALUES(@SectorID, @SubSectorID, @SubSubSectorID, @IsApproved, @ApprovedDate, @ApprovedBy, @InvoiceNo, @InvoiceDate, @InvoiceValue, @PaymentNo, @PaymentDate, @PaymentValue, @RegistrationStatusID, @DebtorSectorID, @RegistrationID, @RegistrationDate, @EmisionTypeID, @EmisionValue, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy, @RegistrationEmisionTypeID)";
                    SqlCommand cmdDetail = new SqlCommand(strCommandDetail, conn);
                    cmdDetail.Parameters.Add("@SectorID", SqlDbType.Int).Value = details.SectorID;
                    cmdDetail.Parameters.Add("@SubSectorID", SqlDbType.Int).Value = details.SubSectorID;
                    cmdDetail.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = details.SubSubSectorID;
                    cmdDetail.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = (object)details.IsApproved ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@ApprovedDate", SqlDbType.DateTime).Value = (object)details.ApprovedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@ApprovedBy", SqlDbType.VarChar).Value = (object)details.ApprovedBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)details.InvoiceNo ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = (object)details.InvoiceDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceValue", SqlDbType.Decimal).Value = (object)details.InvoiceValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)details.PaymentNo ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)details.PaymentDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)details.PaymentValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@RegistrationStatusID", SqlDbType.Int).Value = details.RegistrationStatusID;
                    cmdDetail.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)details.DebtorSectorID ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@RegistrationID", SqlDbType.Int).Value = details.RegistrationID;
                    cmdDetail.Parameters.Add("@RegistrationDate", SqlDbType.DateTime).Value = (object)details.RegistrationDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@EmisionTypeID", SqlDbType.SmallInt).Value = (object)details.EmisionTypeID ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@EmisionValue", SqlDbType.Money).Value = (object)details.EmisionValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)details.CreatedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)details.CreatedBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)details.UpdatedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)details.UpdatedBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@RegistrationEmisionTypeID", SqlDbType.SmallInt).Value = (object)details.RegistrationEmisionTypeID ?? DBNull.Value;


                    cmdDetail.Transaction = txn;
                    cmdDetail.ExecuteNonQuery(); // Save Detail
                    cmdDetail.Dispose();

                    string strCommandUser = "INSERT INTO master.recUser(UserCode, UserName, Email, Password, IsActive, Mobile, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate, UserTypeID) VALUES(@UserCode, @UserName, @Email, @Password, @IsActive, @Mobile, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate, @UserTypeID)";
                    SqlCommand cmdUser = new SqlCommand(strCommandUser, conn);
                    cmdUser.Parameters.Add("@UserCode", SqlDbType.VarChar).Value = user.UserCode;
                    cmdUser.Parameters.Add("@UserName", SqlDbType.VarChar).Value = (object)user.UserName ?? DBNull.Value;
                    cmdUser.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)user.Email ?? DBNull.Value;
                    cmdUser.Parameters.Add("@Password", SqlDbType.VarChar).Value = user.Password;
                    cmdUser.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)user.IsActive ?? DBNull.Value;
                    cmdUser.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = (object)user.Mobile ?? DBNull.Value;
                    cmdUser.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)user.CreatedBy ?? DBNull.Value;
                    cmdUser.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)user.CreatedDate ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)user.UpdatedBy ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)user.UpdatedDate ?? DBNull.Value;
                    cmdUser.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)user.UserTypeID ?? DBNull.Value;
                    cmdUser.Transaction = txn;
                    cmdUser.ExecuteNonQuery();
                    cmdUser.Dispose();

                    string strCommandGetUserId = " SELECT MAX(UserID) UserID FROM master.recUser WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetUserId = new SqlCommand(strCommandGetUserId, conn);
                    cmdGetUserId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = user.CreatedBy;
                    cmdGetUserId.Transaction = txn;
                    var objUserId = cmdGetUserId.ExecuteScalar(); // Get User Id
                    cmdGetUserId.Dispose();

                    decimal UserId = objUserId == DBNull.Value ? 0m : Convert.ToDecimal(objUserId.ToString()); // Get User Id

                    string strCommandGetSectorRegistrationId = " SELECT MAX(SectorRegistrationID) SectorRegistrationID FROM transactions.recSectorRegistration WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetSectorRegistrationId = new SqlCommand(strCommandGetSectorRegistrationId, conn);
                    cmdGetSectorRegistrationId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = details.CreatedBy;
                    cmdGetSectorRegistrationId.Transaction = txn;
                    var objSectorRegistrationId = cmdGetSectorRegistrationId.ExecuteScalar(); // Get SectorRegistrationId
                    cmdGetSectorRegistrationId.Dispose();

                    decimal SectorRegistrationId = objSectorRegistrationId == DBNull.Value ? 0m : Convert.ToDecimal(objSectorRegistrationId.ToString()); // Get SectorRegistrationId

                    string strCommandUserSector = "INSERT INTO master.recUserSector(RegisterSectorID, DebtorSectorID, SubSubSectorID, UserTypeID, UserID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@RegisterSectorID, @DebtorSectorID, @SubSubSectorID, @UserTypeID, @UserID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdUserSector = new SqlCommand(strCommandUserSector, conn);
                    cmdUserSector.Parameters.Add("@RegisterSectorID", SqlDbType.Int).Value = Convert.ToInt32(SectorRegistrationId);
                    cmdUserSector.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)userSector.DebtorSectorID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = details.SubSubSectorID;
                    cmdUserSector.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)userSector.UserTypeID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UserID", SqlDbType.Int).Value = Convert.ToInt32(UserId);
                    cmdUserSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)userSector.CreatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)userSector.CreatedBy ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)userSector.UpdatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)userSector.UpdatedBy ?? DBNull.Value;
                    cmdUserSector.Transaction = txn;
                    cmdUserSector.ExecuteNonQuery();
                    cmdUserSector.Dispose();

                    string strInvoice = "INSERT INTO transactions.recInvoice(InvoiceNo, InvoiceDate, InvoiceValue, BankID, BeSpelled, PaymentNo, PaymentDate, PaymentValue, IsHapusBuku, IsHapusTagih, DebtorSectorID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, Balance) VALUES(@InvoiceNo, @InvoiceDate, @InvoiceValue, @BankID, @BeSpelled, @PaymentNo, @PaymentDate, @PaymentValue, @IsHapusBuku, @IsHapusTagih, @DebtorSectorID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy, @Balance)";
                    SqlCommand cmdInvoice = new SqlCommand(strInvoice, conn);
                    cmdInvoice.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoice.InvoiceNo;
                    //cmdInvoice.Parameters.Add("@PeriodYear", SqlDbType.Int).Value = invoice.PeriodYear;
                    //cmdInvoice.Parameters.Add("@PeriodMonth", SqlDbType.SmallInt).Value = invoice.PeriodMonth;
                    //cmdInvoice.Parameters.Add("@TransDate", SqlDbType.DateTime).Value = invoice.TransDate;
                    cmdInvoice.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = invoice.InvoiceDate;
                    cmdInvoice.Parameters.Add("@InvoiceValue", SqlDbType.Money).Value = (object)invoice.InvoiceValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@BankID", SqlDbType.SmallInt).Value = invoice.BankID;
                    cmdInvoice.Parameters.Add("@BeSpelled", SqlDbType.VarChar).Value = (object)invoice.BeSpelled ?? DBNull.Value;
                    //cmdInvoice.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)invoice.ForPayment ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)invoice.PaymentNo ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)invoice.PaymentDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)invoice.PaymentValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@IsHapusBuku", SqlDbType.Bit).Value = (object)invoice.IsHapusBuku ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@IsHapusTagih", SqlDbType.Bit).Value = (object)invoice.IsHapusTagih ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)invoice.DebtorSectorID ?? -1;
                    //cmdInvoice.Parameters.Add("@PostingDate", SqlDbType.DateTime).Value = (object)invoice.PostingDate ?? DBNull.Value;
                    //cmdInvoice.Parameters.Add("@PostingBy", SqlDbType.VarChar).Value = (object)invoice.PostingBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)invoice.CreatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)invoice.CreatedBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)invoice.UpdatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoice.UpdatedBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@Balance", SqlDbType.Money).Value = (object)invoice.Balance ?? DBNull.Value;
                    cmdInvoice.Transaction = txn;
                    cmdInvoice.ExecuteNonQuery();//save invoice
                    cmdInvoice.Dispose();

                    string strInvoiceDetail = "INSERT INTO transactions.recInvoiceDetail(InvoiceNo, Value, TransTypeID, TariffID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, ForPayment, DueDate) VALUES(@InvoiceNo, @Value, @TransTypeID, @TariffID, @CreateDate, @CreateBy, @UpdateDate, @UpdateBy, @ForPayment, @DueDate)";
                    SqlCommand cmdInvoiceDetail = new SqlCommand(strInvoiceDetail, conn);
                    //cmdInvoiceDetail.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = invoiceDetail.InvoiceDetailID;
                    cmdInvoiceDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoiceDetail.InvoiceNo;
                    cmdInvoiceDetail.Parameters.Add("@Value", SqlDbType.Money).Value = invoiceDetail.Value;
                    cmdInvoiceDetail.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = invoiceDetail.TransTypeID;
                    cmdInvoiceDetail.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)invoiceDetail.TariffID ?? DBNull.Value;
                    //cmdInvoiceDetail.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = (object)invoiceDetail.CreatedDate ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = (object)invoiceDetail.CreatedBy ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)invoiceDetail.UpdatedDate ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)invoiceDetail.UpdatedBy ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)invoiceDetail.ForPayment ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = (object)invoiceDetail.DueDate ?? DBNull.Value;
                    cmdInvoiceDetail.Transaction = txn;
                    cmdInvoiceDetail.ExecuteNonQuery();//save invoice detail
                    cmdInvoiceDetail.Dispose();

                    string strGetInvoiceDetailId = " SELECT MAX(InvoiceDetailID) InvoiceDetailID FROM transactions.recInvoiceDetail WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetInvoiceDetailId = new SqlCommand(strGetInvoiceDetailId, conn);
                    cmdGetInvoiceDetailId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = invoiceDetail.CreatedBy;
                    cmdGetInvoiceDetailId.Transaction = txn;
                    var objInvoiceDetailId = cmdGetInvoiceDetailId.ExecuteScalar(); // Get SectorRegistrationId
                    cmdGetInvoiceDetailId.Dispose();

                    decimal InvoiceDetailId = objInvoiceDetailId == DBNull.Value ? 0m : Convert.ToDecimal(objInvoiceDetailId.ToString()); // Get InvoiceDetailId

                    string strBilling = "INSERT INTO transactions.recBilling(BillingNo, DebtorSectorID, TransTypeID, TariffID, Value, InvoiceNo, InvoiceDetailID, BillingDate,CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, DueDate, ForPayment, Balance) VALUES(@BillingNo, @DebtorSectorID, @TransTypeID, @TariffID, @Value, @InvoiceNo, @InvoiceDetailID, @BillingDate, @CreateDate, @CreateBy, @UpdateDate, @UpdateBy, @DueDate, @ForPayment, @Balance)";
                    SqlCommand cmdBilling = new SqlCommand(strBilling, conn);
                    cmdBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = billing.DebtorSectorID;
                    cmdBilling.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = billing.TransTypeID;
                    cmdBilling.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)billing.TariffID ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@Value", SqlDbType.Money).Value = billing.Value;
                    cmdBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)billing.InvoiceNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = (object)InvoiceDetailId ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@BillingDate", SqlDbType.DateTime).Value = (object)billing.BillingDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = (object)billing.CreatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = (object)billing.CreatedBy ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)billing.UpdatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)billing.UpdatedBy ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = (object)billing.DueDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)billing.ForPayment ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@Balance", SqlDbType.Money).Value = (object)billing.Balance ?? DBNull.Value;
                    cmdBilling.Transaction = txn;
                    cmdBilling.ExecuteNonQuery();//save billing
                    cmdBilling.Dispose();

                    string strUpdateInvoiceDetail = "UPDATE transactions.recInvoiceDetail SET BillingNo = @BillingNo ,UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy where InvoiceNo = @InvoiceNo ";
                    SqlCommand cmdUpdateInvoiceDetail = new SqlCommand(strUpdateInvoiceDetail, conn);
                    cmdUpdateInvoiceDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoiceDetail.InvoiceNo;
                    cmdUpdateInvoiceDetail.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdUpdateInvoiceDetail.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = DateTime.Now;
                    cmdUpdateInvoiceDetail.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoiceDetail.CreatedBy ?? DBNull.Value;
                    cmdUpdateInvoiceDetail.Transaction = txn;
                    cmdUpdateInvoiceDetail.ExecuteNonQuery();//update invoicedetail
                    cmdUpdateInvoiceDetail.Dispose();

                    string struserrole = "INSERT INTO master.recUserRole(UserID, RoleID, CreatedDate, CreatedBy) VALUES(@UserID, @RoleID, @CreatedDate, @CreatedBy)";
                    SqlCommand cmdUserRole = new SqlCommand(struserrole, conn);
                    cmdUserRole.Parameters.Add("@UserID", SqlDbType.Int).Value = Convert.ToInt32(UserId);
                    cmdUserRole.Parameters.Add("@RoleID", SqlDbType.Int).Value = (object)userRole.RoleID ?? DBNull.Value;
                    cmdUserRole.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)userRole.CreatedDate ?? DBNull.Value;
                    cmdUserRole.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)userRole.CreatedBy ?? DBNull.Value;
                    cmdUserRole.Transaction = txn;
                    cmdUserRole.ExecuteNonQuery();//save user role
                    cmdUserRole.Dispose();

                    result = true;
                    txn.Commit();
                }

                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }

        public bool Save(recDebtor debtor, recSectorRegistration details, recUserSector userSector, recInvoice invoice, recInvoiceDetail invoiceDetail, recBilling billing)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strUpdateDebtor = "UPDATE master.recDebtor SET  DebtorName = @DebtorName, NPWP = @NPWP, Address1 = @Address1, Address2 = @Address2, Address3 = @Address3, ZipCode = @ZipCode, RegionID = @RegionID, CityID = @CityID, Email = @Email, Phone = @Phone, Fax = @Fax, Website = @Website, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE DebtorID = @DebtorID ";
                    SqlCommand cmdDebtor = new SqlCommand(strUpdateDebtor, conn);
                    cmdDebtor.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtor.DebtorID;
                    //cmdDebtor.Parameters.Add("@DebtorCode", SqlDbType.VarChar).Value = debtor.DebtorCode;
                    cmdDebtor.Parameters.Add("@DebtorName", SqlDbType.VarChar).Value = debtor.DebtorName;
                    cmdDebtor.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = debtor.NPWP;
                    cmdDebtor.Parameters.Add("@Address1", SqlDbType.VarChar).Value = (object)debtor.Address1 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Address2", SqlDbType.VarChar).Value = (object)debtor.Address2 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Address3", SqlDbType.VarChar).Value = (object)debtor.Address3 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@ZipCode", SqlDbType.VarChar).Value = (object)debtor.ZipCode ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@RegionID", SqlDbType.Int).Value = (object)debtor.RegionID ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@CityID", SqlDbType.Int).Value = (object)debtor.CityID ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)debtor.Email ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Phone", SqlDbType.VarChar).Value = (object)debtor.Phone ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Fax", SqlDbType.VarChar).Value = (object)debtor.Fax ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Website", SqlDbType.VarChar).Value = (object)debtor.Website ?? DBNull.Value;
                    //cmdDebtor.Parameters.Add("@IsActive", SqlDbType.Bit).Value = debtor.IsActive;
                    cmdDebtor.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)debtor.UpdatedDate ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)debtor.UpdatedBy ?? DBNull.Value;
                    //cmdDebtor.Parameters.Add("@IsIndividual", SqlDbType.Bit).Value = debtor.IsIndividual;
                    cmdDebtor.Transaction = txn;
                    cmdDebtor.ExecuteNonQuery(); // update Debtor
                    cmdDebtor.Dispose();

                    string strCommandDetail = "INSERT INTO transactions.recSectorRegistration(SectorID, SubSectorID, SubSubSectorID, IsApproved, ApprovedDate, ApprovedBy, InvoiceNo, InvoiceDate, InvoiceValue, PaymentNo, PaymentDate, PaymentValue, RegistrationStatusID, DebtorSectorID, RegistrationID, RegistrationDate, EmisionTypeID, EmisionValue, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@SectorID, @SubSectorID, @SubSubSectorID, @IsApproved, @ApprovedDate, @ApprovedBy, @InvoiceNo, @InvoiceDate, @InvoiceValue, @PaymentNo, @PaymentDate, @PaymentValue, @RegistrationStatusID, @DebtorSectorID, @RegistrationID, @RegistrationDate, @EmisionTypeID, @EmisionValue, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdDetail = new SqlCommand(strCommandDetail, conn);
                    cmdDetail.Parameters.Add("@SectorID", SqlDbType.Int).Value = details.SectorID;
                    cmdDetail.Parameters.Add("@SubSectorID", SqlDbType.Int).Value = details.SubSectorID;
                    cmdDetail.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = details.SubSubSectorID;
                    cmdDetail.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = (object)details.IsApproved ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@ApprovedDate", SqlDbType.DateTime).Value = (object)details.ApprovedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@ApprovedBy", SqlDbType.VarChar).Value = (object)details.ApprovedBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)details.InvoiceNo ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = (object)details.InvoiceDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@InvoiceValue", SqlDbType.Decimal).Value = (object)details.InvoiceValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)details.PaymentNo ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)details.PaymentDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)details.PaymentValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@RegistrationStatusID", SqlDbType.Int).Value = details.RegistrationStatusID;
                    cmdDetail.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)details.DebtorSectorID ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@RegistrationID", SqlDbType.Int).Value = details.RegistrationID;
                    cmdDetail.Parameters.Add("@RegistrationDate", SqlDbType.DateTime).Value = (object)details.RegistrationDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@EmisionTypeID", SqlDbType.SmallInt).Value = (object)details.EmisionTypeID ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@EmisionValue", SqlDbType.Money).Value = (object)details.EmisionValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)details.CreatedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)details.CreatedBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)details.UpdatedDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)details.UpdatedBy ?? DBNull.Value;

                    cmdDetail.Transaction = txn;
                    cmdDetail.ExecuteNonQuery(); // Save Detail
                    cmdDetail.Dispose();

                    string strCommandGetSectorRegistrationId = " SELECT MAX(SectorRegistrationID) SectorRegistrationID FROM transactions.recSectorRegistration WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetSectorRegistrationId = new SqlCommand(strCommandGetSectorRegistrationId, conn);
                    cmdGetSectorRegistrationId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = details.CreatedBy;
                    cmdGetSectorRegistrationId.Transaction = txn;
                    var objSectorRegistrationId = cmdGetSectorRegistrationId.ExecuteScalar(); // Get SectorRegistrationId
                    cmdGetSectorRegistrationId.Dispose();

                    decimal SectorRegistrationId = objSectorRegistrationId == DBNull.Value ? 0m : Convert.ToDecimal(objSectorRegistrationId.ToString()); // Get SectorRegistrationId

                    string strCommandUserSector = "INSERT INTO master.recUserSector(RegisterSectorID, DebtorSectorID, SubSubSectorID, UserTypeID, UserID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@RegisterSectorID, @DebtorSectorID, @SubSubSectorID, @UserTypeID, @UserID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdUserSector = new SqlCommand(strCommandUserSector, conn);
                    cmdUserSector.Parameters.Add("@RegisterSectorID", SqlDbType.Int).Value = Convert.ToInt32(SectorRegistrationId);
                    cmdUserSector.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)userSector.DebtorSectorID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = details.SubSubSectorID;
                    cmdUserSector.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = (object)userSector.UserTypeID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UserID", SqlDbType.Int).Value = (object)userSector.UserID ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)userSector.CreatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)userSector.CreatedBy ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)userSector.UpdatedDate ?? DBNull.Value;
                    cmdUserSector.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)userSector.UpdatedBy ?? DBNull.Value;
                    cmdUserSector.Transaction = txn;
                    cmdUserSector.ExecuteNonQuery();
                    cmdUserSector.Dispose();

                    string strInvoice = "INSERT INTO transactions.recInvoice(InvoiceNo, InvoiceDate, InvoiceValue, BankID, BeSpelled, PaymentNo, PaymentDate, PaymentValue, IsHapusBuku, IsHapusTagih, DebtorSectorID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@InvoiceNo, @InvoiceDate, @InvoiceValue, @BankID, @BeSpelled, @PaymentNo, @PaymentDate, @PaymentValue, @IsHapusBuku, @IsHapusTagih, @DebtorSectorID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    SqlCommand cmdInvoice = new SqlCommand(strInvoice, conn);
                    cmdInvoice.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoice.InvoiceNo;
                    //cmdInvoice.Parameters.Add("@PeriodYear", SqlDbType.Int).Value = invoice.PeriodYear;
                    //cmdInvoice.Parameters.Add("@PeriodMonth", SqlDbType.SmallInt).Value = invoice.PeriodMonth;
                    //cmdInvoice.Parameters.Add("@TransDate", SqlDbType.DateTime).Value = invoice.TransDate;
                    cmdInvoice.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = invoice.InvoiceDate;
                    cmdInvoice.Parameters.Add("@InvoiceValue", SqlDbType.Money).Value = (object)invoice.InvoiceValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@BankID", SqlDbType.SmallInt).Value = invoice.BankID;
                    cmdInvoice.Parameters.Add("@BeSpelled", SqlDbType.VarChar).Value = (object)invoice.BeSpelled ?? DBNull.Value;
                    //cmdInvoice.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)invoice.ForPayment ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)invoice.PaymentNo ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)invoice.PaymentDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@PaymentValue", SqlDbType.Money).Value = (object)invoice.PaymentValue ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@IsHapusBuku", SqlDbType.Bit).Value = (object)invoice.IsHapusBuku ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@IsHapusTagih", SqlDbType.Bit).Value = (object)invoice.IsHapusTagih ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)invoice.DebtorSectorID ?? -1;
                    //cmdInvoice.Parameters.Add("@PostingDate", SqlDbType.DateTime).Value = (object)invoice.PostingDate ?? DBNull.Value;
                    //cmdInvoice.Parameters.Add("@PostingBy", SqlDbType.VarChar).Value = (object)invoice.PostingBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)invoice.CreatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)invoice.CreatedBy ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)invoice.UpdatedDate ?? DBNull.Value;
                    cmdInvoice.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoice.UpdatedBy ?? DBNull.Value;
                    cmdInvoice.Transaction = txn;
                    cmdInvoice.ExecuteNonQuery();//save invoice
                    cmdInvoice.Dispose();

                    string strInvoiceDetail = "INSERT INTO transactions.recInvoiceDetail(InvoiceNo, Value, TransTypeID, TariffID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@InvoiceNo, @Value, @TransTypeID, @TariffID, @CreateDate, @CreateBy, @UpdateDate, @UpdateBy)";
                    SqlCommand cmdInvoiceDetail = new SqlCommand(strInvoiceDetail, conn);
                    //cmdInvoiceDetail.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = invoiceDetail.InvoiceDetailID;
                    cmdInvoiceDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoiceDetail.InvoiceNo;
                    cmdInvoiceDetail.Parameters.Add("@Value", SqlDbType.Money).Value = invoiceDetail.Value;
                    cmdInvoiceDetail.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = invoiceDetail.TransTypeID;
                    cmdInvoiceDetail.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)invoiceDetail.TariffID ?? DBNull.Value;
                    //cmdInvoiceDetail.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = (object)invoiceDetail.CreatedDate ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = (object)invoiceDetail.CreatedBy ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)invoiceDetail.UpdatedDate ?? DBNull.Value;
                    cmdInvoiceDetail.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)invoiceDetail.UpdatedBy ?? DBNull.Value;
                    cmdInvoiceDetail.Transaction = txn;
                    cmdInvoiceDetail.ExecuteNonQuery();//save invoice detail
                    cmdInvoiceDetail.Dispose();

                    string strGetInvoiceDetailId = " SELECT MAX(InvoiceDetailID) InvoiceDetailID FROM transactions.recInvoiceDetail WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetInvoiceDetailId = new SqlCommand(strGetInvoiceDetailId, conn);
                    cmdGetInvoiceDetailId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = invoiceDetail.CreatedBy;
                    cmdGetInvoiceDetailId.Transaction = txn;
                    var objInvoiceDetailId = cmdGetInvoiceDetailId.ExecuteScalar(); // Get SectorRegistrationId
                    cmdGetInvoiceDetailId.Dispose();

                    decimal InvoiceDetailId = objInvoiceDetailId == DBNull.Value ? 0m : Convert.ToDecimal(objInvoiceDetailId.ToString()); // Get InvoiceDetailId

                    string strBilling = "INSERT INTO transactions.recBilling(BillingNo, DebtorSectorID, TransTypeID, TariffID, Value, InvoiceNo, InvoiceDetailID, BillingDate,CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, DueDate) VALUES(@BillingNo, @DebtorSectorID, @TransTypeID, @TariffID, @Value, @InvoiceNo, @InvoiceDetailID, @BillingDate, @CreateDate, @CreateBy, @UpdateDate, @UpdateBy, @DueDate)";
                    SqlCommand cmdBilling = new SqlCommand(strBilling, conn);
                    cmdBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = billing.DebtorSectorID;
                    cmdBilling.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = billing.TransTypeID;
                    cmdBilling.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)billing.TariffID ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@Value", SqlDbType.Money).Value = billing.Value;
                    cmdBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)billing.InvoiceNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = (object)InvoiceDetailId ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@BillingDate", SqlDbType.DateTime).Value = (object)billing.BillingDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = (object)billing.CreatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = (object)billing.CreatedBy ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)billing.UpdatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)billing.UpdatedBy ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = (object)billing.DueDate ?? DBNull.Value;
                    cmdBilling.Transaction = txn;
                    cmdBilling.ExecuteNonQuery();//save billing
                    cmdBilling.Dispose();

                    string strUpdateInvoiceDetail = "UPDATE transactions.recInvoiceDetail SET BillingNo = @BillingNo ,UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy where InvoiceNo = @InvoiceNo ";
                    SqlCommand cmdUpdateInvoiceDetail = new SqlCommand(strUpdateInvoiceDetail, conn);
                    cmdUpdateInvoiceDetail.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoiceDetail.InvoiceNo;
                    cmdUpdateInvoiceDetail.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmdUpdateInvoiceDetail.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = DateTime.Now;
                    cmdUpdateInvoiceDetail.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoiceDetail.CreatedBy ?? DBNull.Value;
                    cmdUpdateInvoiceDetail.Transaction = txn;
                    cmdUpdateInvoiceDetail.ExecuteNonQuery();//update invoicedetail
                    cmdUpdateInvoiceDetail.Dispose();

                    result = true;
                    txn.Commit();
                }

                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }
        public bool Update(recDebtor debtor)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strUpdateDebtor = "UPDATE master.recDebtor SET  DebtorName = @DebtorName, NPWP = @NPWP, Address1 = @Address1, Address2 = @Address2, Address3 = @Address3, ZipCode = @ZipCode, RegionID = @RegionID, CityID = @CityID, Email = @Email, Phone = @Phone, Fax = @Fax, Website = @Website, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE DebtorID = @DebtorID ";
                    SqlCommand cmdDebtor = new SqlCommand(strUpdateDebtor, conn);
                    cmdDebtor.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtor.DebtorID;
                    //cmdDebtor.Parameters.Add("@DebtorCode", SqlDbType.VarChar).Value = debtor.DebtorCode;
                    cmdDebtor.Parameters.Add("@DebtorName", SqlDbType.VarChar).Value = debtor.DebtorName;
                    cmdDebtor.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = debtor.NPWP;
                    cmdDebtor.Parameters.Add("@Address1", SqlDbType.VarChar).Value = (object)debtor.Address1 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Address2", SqlDbType.VarChar).Value = (object)debtor.Address2 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Address3", SqlDbType.VarChar).Value = (object)debtor.Address3 ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@ZipCode", SqlDbType.VarChar).Value = (object)debtor.ZipCode ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@RegionID", SqlDbType.Int).Value = (object)debtor.RegionID ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@CityID", SqlDbType.Int).Value = (object)debtor.CityID ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)debtor.Email ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Phone", SqlDbType.VarChar).Value = (object)debtor.Phone ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Fax", SqlDbType.VarChar).Value = (object)debtor.Fax ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@Website", SqlDbType.VarChar).Value = (object)debtor.Website ?? DBNull.Value;
                    //cmdDebtor.Parameters.Add("@IsActive", SqlDbType.Bit).Value = debtor.IsActive;
                    cmdDebtor.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)debtor.UpdatedDate ?? DBNull.Value;
                    cmdDebtor.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)debtor.UpdatedBy ?? DBNull.Value;
                    //cmdDebtor.Parameters.Add("@IsIndividual", SqlDbType.Bit).Value = debtor.IsIndividual;
                    cmdDebtor.Transaction = txn;
                    cmdDebtor.ExecuteNonQuery(); // update Debtor
                    cmdDebtor.Dispose();

                    result = true;
                    txn.Commit();
                }

                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }

        public recRegistration GetByUserID(int key)
        {
            recRegistration t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                StringBuilder sbQuery = new StringBuilder();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    sbQuery.AppendFormat("select top 1 a.SectorID,a.SectorCode,a.SectorName, {0}", Environment.NewLine);
                    sbQuery.AppendFormat("a.SubSectorID,a.SubSectorCode,a.SubSectorName, a.SubSubSectorID,a.SubSubSectorCode,a.SubSubSectorName from ( {0}", Environment.NewLine);
                    sbQuery.AppendFormat("select transactions.recSectorRegistration.SectorID,master.recSector.SectorCode,master.recSector.SectorName, {0}", Environment.NewLine);
                    sbQuery.AppendFormat("transactions.recSectorRegistration.SubSectorID,master.recSubSector.SubSectorCode,master.recSubSector.SubSectorName, {0}", Environment.NewLine);
                    sbQuery.AppendFormat("transactions.recSectorRegistration.SubSubSectorID,master.recSubSubSector.SubSubSectorCode,master.recSubSubSector.SubSubSectorName from transactions.recRegistration  {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join transactions.recSectorRegistration on transactions.recSectorRegistration.RegistrationID=transactions.recRegistration.RegistrationID  {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recUserSector on master.recUserSector.RegisterSectorID=transactions.recSectorRegistration.SectorRegistrationID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recSubSector on master.recSubSector.SubSectorID=transactions.recSectorRegistration.SubSectorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recSector on master.recSector.SectorID=transactions.recSectorRegistration.SectorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recSubSubSector on master.recSubSubSector.SubSubSectorID=transactions.recSectorRegistration.SubSubSectorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("where master.recUserSector.UserID={0} and (master.recSubSector.SubSectorName like LOWER('emiten%') or master.recSubSector.SubSectorName like LOWER('perusahaan publik%')) {1}", key, Environment.NewLine);
                    sbQuery.AppendFormat("union {0}", Environment.NewLine);
                    sbQuery.AppendFormat("select master.recDebtorSector.SectorID,master.recSector.SectorCode,master.recSector.SectorName, {0}", Environment.NewLine);
                    sbQuery.AppendFormat("master.recDebtorSector.SubSectorID,master.recSubSector.SubSectorCode,master.recSubSector.SubSectorName, {0}", Environment.NewLine);
                    sbQuery.AppendFormat("master.recDebtorSector.SubSubSectorID,master.recSubSubSector.SubSubSectorCode,master.recSubSubSector.SubSubSectorName from master.recDebtor {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recDebtorSector on master.recDebtor.DebtorID=master.recDebtorSector.DebtorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recSubSector on master.recSubSector.SubSectorID=master.recDebtorSector.SubSectorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recSector on master.recSector.SectorID=master.recDebtorSector.SectorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recSubSubSector on master.recSubSubSector.SubSubSectorID=master.recDebtorSector.SubSubSectorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recUser on master.recUser.DebtorID=master.recDebtor.DebtorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("where master.recUser.UserID={0} and (master.recSubSector.SubSectorName like LOWER('emiten%') or master.recSubSector.SubSectorName like LOWER('perusahaan publik%'))) a {1}", key, Environment.NewLine);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recRegistration(true);
                                t.recSector.SectorID = dr["SectorID"] == DBNull.Value ? 0 : int.Parse(dr["SectorID"].ToString());
                                t.recSector.SectorCode = dr["SectorCode"] == DBNull.Value ? string.Empty : dr["SectorCode"].ToString();
                                t.recSector.SectorName = dr["SectorName"] == DBNull.Value ? string.Empty : dr["SectorName"].ToString();
                                t.recSubSector.SubSectorID = dr["SubSectorID"] == DBNull.Value ? 0 : int.Parse(dr["SubSectorID"].ToString());
                                t.recSubSector.SubSectorCode = dr["SubSectorCode"] == DBNull.Value ? string.Empty : dr["SubSectorCode"].ToString();
                                t.recSubSector.SubSectorName = dr["SubSectorName"] == DBNull.Value ? string.Empty : dr["SubSectorName"].ToString();
                                t.recSubSubSector.SubSubSectorID = dr["SubSubSectorID"] == DBNull.Value ? 0 : int.Parse(dr["SubSubSectorID"].ToString());
                                t.recSubSubSector.SubSubSectorCode = dr["SubSubSectorCode"] == DBNull.Value ? string.Empty : dr["SubSubSectorCode"].ToString();
                                t.recSubSubSector.SubSubSectorName = dr["SubSubSectorName"] == DBNull.Value ? string.Empty : dr["SubSubSectorName"].ToString();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

    }

}
